Database context within cursor fetch loop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Database context within cursor fetch loop

Author

Topic

jameswithers
Starting Member

4 Posts

Posted - 2008-04-18 : 08:47:35

Hi,
I am new to transact-sql and this is probably a stupid question. We have a very large number of sql installations and databases and I have a task to change the db owner on every database we have. I am attempting to write a script that will change the owner for all databases in an installation. I am trying to use sp_changedbowner however it does not take a database argument and expects to already be in the database context. I am trying unsuccessfully to use "USE Database" within a cursor loop but this does not work as expected. Does anyone have any ideas as to how I can change the database context, the code I have written is below?

Thanks

declare chdbownCur cursor static
for select name from master.dbo.sysdatabases
fetch next from chdbownCur into @dbName
while @@fetch_status = 0
begin
use @dbName
go
exec sp_changedbowner 'hss\sa_fleetwise'"
fetch next from chdbownCur into @dbName
end
close chdbownCur
deallocate chdbownCur

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-18 : 08:53:18

You can make use of sp_MSforeachdb...

http://www.databasejournal.com/features/mssql/article.php/3441031

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

jameswithers
Starting Member

4 Posts

Posted - 2008-04-18 : 09:05:33

Thanks for the advice Ryan, I will give this a shot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources