Exec sp_changedbowner from another DB

Question

Exec sp_changedbowner from another DB

Simon-220850

SSCrazy

Points: 2789
More actions
June 28, 2006 at 7:03 am

#61438

Hi
I am creating a stored procedure which will reside in a DBA utility database on our server. I wish part of this SP to be able to change the dbowner on another database (passed as a parameter)
Is it possible to do this ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Simon

According to Books Online, sp_changedbowner "Changes the owner of the current database". So the answer to your question is no if you want to use that stored procedure. However, have a look at what that SP does and see if you can modify it to meet your needs. Don't do it on a production box, though, until you've tested it thoroughly. And, if it breaks, Microsoft probably won't support you if you've fiddled with the system stored procedures.

John

Simon-220850 SSCrazy Points: 2789 More actions · Answer 2

With a bit of Jiggery Pokery, I've managed to get a solution using xp_cmdshell and osql

DECLARE @dboname varchar(200)

DECLARE @CMD VARCHAR(200)

DECLARE @OSQL VARCHAR(200)

SET @dboname = 'NewDBOName'

SELECT @CMD = 'USE ' + @dboname

SELECT @CMD = @CMD + ' EXEC sp_changedbowner ' + @dboname + ''

SET @OSQL = 'exec master..xp_cmdshell ''' + 'OSQL -E -S Servername -Q"' + @cmd + '" '' '

EXEC (@osql)

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

You do not have to go out to xp_cmdshell. One must merely 'qualify' the execution. An example I use all of the time:

use master

go

exec some_other_database..sp_changedbowner 'sa'

go

This works well. Now all you have to do is add the dynamic SQL portion for a bit of pizazz:

declare @dbname varchar(32)

declare @dynamic_sql varchar(255)

select @dbname='dba_info_db'

select @dynamic_sql='exec ' + @dbname + '..sp_changedbowner ''sa'''

print @dynamic_sql

exec ( @dynamic_sql )

and the results

exec dba_info_db..sp_changedbowner 'sa'

The dependent aliases were mapped to the new database owner.

Database owner changed.:

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Simon-220850 SSCrazy Points: 2789 More actions · Answer 4

Ahh, thanks Rudy, I had made the mistaken assumption that sp_changedbowner was a system sp that was only in the master database

Cheers

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

Simon your assumption is correct - sp_changfedbowner is a system stored procedure and only exists in the master database. It's just the 'execution context' that needed fixing.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."