Wednesday, August 26, 2009

Who owns my database?

We often get database generated by a TestLab group, or QA group. They can have VMs cranked out with cookie cutter databases on them. We will get these dbs, apply pieces, and hand them on to the next group. This is a process that one becomes accustomed too, and one ends up not noticing the small things, like who owns the database.
So, we often get to verify that the correct owner in fact owns it. However, its usually a quick task that forces one to simply rely on the UI to perform the verification and subsequent alterations. Using the UI is not a bad thing. But i was taught early in my career to not trust the UI, but to learn what is being done underneath, dig into it, understand it, and if possible script out the actions.
I do love that management studio allows for much more scripting than previous UI tools did. I tend to use this and profiler to perform the snooping, then i massage the results into a script or job or something that i can use to repeat.

Today, i finally got tired of using the UI to verify the owner of the database. I have previously used the wonderful proc sp_changedbowner to alter the ownership. And often, simply cheated, and assigned it to 'sa' without even checking if it was owned or not by 'sa'. I dug into some procs, traced some actions of Management Studio, and saved me a simple script that can validate the owner, and even spit out for me the subsequent change sql needed to alter owner. I'm only sharing this with the rest of you because, if you are like me, you like having these scripts handy. Maybe they will help you out. Someday, you'll remember this, look for it, and viola! save yourself precious seconds or minutes.

So, here is it.

select
sd.name as [DatabaseName],
suser_sname(sd.sid) as [Owner],
case
when suser_sname(sd.sid) <> 'sa' then
'use ' + name + ' exec sp_changedbowner ''sa'''
else ''
end as ChangeDatabaseOwner,
sd.*
from master.dbo.sysdatabases sd
where suser_sname(sd.sid) <> 'sa'


run this with or without the where clause. I like to do both. Take the results, which look like this...


use db1 exec sp_changedbowner 'sa'
use db2 exec sp_changedbowner 'sa'
use db3 exec sp_changedbowner 'sa'
use db4 exec sp_changedbowner 'sa'


and execute them. Not elegant. Not a fancy UI. But little thought needs to go into it. Quick, dirty, and works. I'm saving this into a file in my [_helpers] folder for future use. already, I've quickly altered 50+ databases on 4 db servers. Blamo!

3 comments:

Aaron Bertrand said...

sp_changedbowner is deprecated; going forward you should use:

ALTER AUTHORIZATION ON DATABASE::dbname TO principal;

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=475552

tjaybelt said...

i didnt mention that i am mired in the SQL2000 world still for some of our boxes. Its definitely good to note that a new way to accomplish this task should be used, on newer systems.
thanks for the correction Aaron.

SQL said...

Another SQL 2000 version



sp_msforeachdb 'if (select quotename(name) from master.dbo.sysdatabases sd
where suser_sname(sd.sid) <> ''sa'') = ''[?]'' exec [?]..sp_changedbowner ''sa'' '