Changing Database Owner

[Andel]

Is there a way to change the database owner using sp_changedbowner stored proc without having to be executed in the current database?

I have a script that creates a database and suppose to change the db owner right after creation.
I cannot use the 'USE <Dbname> command because it requires a 'GO' before you can use the Database. I can't use 'GO' inside my stored procs otherwise all my variables will disappear.

Any idea?

 

[SQLBill]

Servername.master.dbo.sp_changedbowner will work. Create a table with all your servernames and a serverID, then create a script like:

DECLARE @script VARCHAR(200)
DECLARE @servernumber INT
DECLARE @servercnt INT
SET @servernumber = (SELECT MAX(serverid) FROM tServerNames)
SET @servercnt = 0
 BEGIN
   WHILE @Servercnt < @servernumber
     SELECT @ServerName = ServerName
      FROM Servertable
       WHERE ServerID = @servercnt
SET @script = 'EXEC ' + @servername + .master.dbo.sp_changedbowner'
    SET @Servercnt = @Servercnt +1
 --PRINT @script
 --EXEC @Script
  END

Test it by uncommenting the PRINT line. Run it by uncommenting the EXEC line. You might have to tweak it some.

-SQLBill



Posting advice: FAQ481-4875

 

[ptheriault]

you could use dynamic sql to build your command and execute it.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

just like Bill did. Nice job Bill. : )

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Andel]

Thanks for you guys response. Bill, i'm not sure how to use your query. I'm trying to change a database owner. Here's my current script (that doesn't work).

CREATE DATABASE MyNewDB
ON PRIMARY
(NAME = Scratch_Data,
FILENAME = 'H:\MSSQL\DATA\MyNewDB.MDF',
SIZE = 5000,
MAXSIZE = 10001,
FILEGROWTH = 10%)
LOG ON
(NAME = Scratch_log,
FILENAME = 'O:\MSSQL\DATA\MyNewDB.LDF',
SIZE = 2500,
MAXSIZE = 7501,
FILEGROWTH = 10%)
--
EXEC('USE MyNewDB: sp_changedbowner [PROD\Andel]')


Another question Bill, how did you format your postings here so that your codes appear inside a box?

 

[SQLBill]

Use the TGML tags. At the bottom of the window where you type your post, there is a link "Process TGML". Click on that and see all the options. I used:

[ignore]

code here

[/ignore]

The [ignore] tag let me show you the actual tags.

-SQLBill

Posting advice: FAQ481-4875