January 28, 2004 at 11:53 am
I'm trying to develop a script in TSQL which employs sp_changedbowner to change all non system databases back to sa but am running into a problem with the 'Use" syntax to switch the database context that I'm in.
Script I've been testing with follows:
Anyone have any ideas to get around this?
Thanks
===============
--create procedure ChgDbOwner as
SET NOCOUNT ON
/* Declare Cursor for Databases to BE EXCLUDED */
DECLARE DatabaseList CURSOR FOR SELECT name FROM master..sysdatabases
WHERE rtrim(lower(name)) not in('master','model','tempdb','msdb')
ORDER BY name
DECLARE @AltrDBStr varchar(200),
@AltrDBStr2 varchar(200),
@DB_Name varchar(50)
OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @db_name
WHILE @@FETCH_STATUS <> -1
BEGIN
PRINT @db_name
select @AltrDBStr2 = 'use ' + @db_name
print @AltrDBStr2
exec(@AltrDBStr2)
select @AltrDBStr = ' sp_changedbowner testme '
print @AltrDBStr
exec(@AltrDBStr)
FETCH NEXT FROM DatabaseList INTO @db_name
END
CLOSE DatabaseList
DEALLOCATE DatabaseList
January 28, 2004 at 12:16 pm
T NOCOUNT ON
/* Declare Cursor for Databases to BE EXCLUDED */
DECLARE DatabaseList CURSOR FOR SELECT name FROM master..sysdatabases
WHERE rtrim(lower(name)) not in('master','model','tempdb','msdb')
ORDER BY name
DECLARE @AltrDBStr varchar(200),
@AltrDBStr2 nvarchar(200),
@DB_Name varchar(50)
OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @db_name
WHILE @@FETCH_STATUS <> -1
BEGIN
select @AltrDBStr2 = "use " + @db_name + " exec sp_changedbowner 'sa'"
print @AltrDBStr2
EXECUTE sp_executesql @AltrDBStr2
FETCH NEXT FROM DatabaseList INTO @db_name
END
CLOSE DatabaseList
DEALLOCATE DatabaseList
January 28, 2004 at 12:48 pm
The new 'look' doesn't allow me to properly format this, so bear with me. There's an 'undocumented' procedure called sp_msforeachdb which allows you to execute upto three commands for every database on the system. Example: EXEC sp_msforeachdb 'USE ?; exec sp_changedbowner 'newname'' The command will replace the ? with each database name. -SQLBill (Credit to Ken Henderson - Guru's Guide to Transact SQL)
January 29, 2004 at 2:04 am
I had to create a procedure recently to do the same thing. I needed to change all tables back to dbo. The ownership of the tables belong to the nt accounts that imported the data into sql.
Here is the sp I created (not pretty but it does just the job!)
CREATE PROCEDURE [dbo].[sp_changeowner] AS
set nocount on
declare @table sysname
declare @sql nvarchar(255)
declare @owner nvarchar(255)
declare service_list cursor for select table_name from INFORMATION_SCHEMA.TABLES where table_schema <>'dbo'
open service_list
fetch next from service_list into @table
while @@fetch_status=0
begin
set @owner = (select (table_schema) from INFORMATION_SCHEMA.TABLES where table_schema <>'dbo' and table_name = @table)
set @table = (select (table_name) from INFORMATION_SCHEMA.TABLES where table_schema <>'dbo' and table_name = @table)
exec ( 'exec sp_changeobjectowner N''[' +@owner + '].'+@table + ''', N''dbo''')
fetch next from service_list into @table
END
deallocate service_list
GO
Hope this helps
Karl
January 29, 2004 at 10:54 am
You could eliminate the USE DB stmt entirely, specifying the target DB instead when executing the sp proc. All master sp procs can be used this way, pretty convenient. This yields one-line solution:
exec sp_MSforeachdb 'if ''?'' not in (''master'',''model'',''tempdb'',''msdb'') exec ?..sp_changedbowner testme', '?'
note: there are no double-quotes above, just single-quotes, sometimes paird
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply