Change DB Owner in SQL Server

Sometimes some hidden configurations mess up with the management of our SQL Database Servers. One great candidate to do this is the “Owner” property of the databases. Not that the property will cause some issue by itself, but the default value that it gets is what will cause the problems.

By default, this property saves the login of the user that created/attached/restored the database. When you have a well established DBA team or access policy, this will not cause any harm. But when the create/attach/restore is done by a migration team or some outsourced personnel, it will: for example you can’t remove a login from SQL Server if it owns any database. First you must remove that ownership and then delete the login.

Because the value of the owner of the database will stay the same unless explicitly changed, there is a great chance that the login stored in there will not have access to the server at some time in the future. This is the same kind of latent problem that happens with SQL Agent Jobs or Maintenance Packages, but in these two cases the error will be caught much faster. In the case of the owner of the database, you may not see any error for years. And then when some error do happen, it will not be catastrophic and is easy to fix by using  the sp_changedbowner system procedure.

Well, it is easy to do this if you have only a few databases. What if you have a hundred of small databases that need to get a new owner ASAP? For this situation I’m sharing a quick and dirty script created just to quickly change the owner of all databases owned by a specific user. Note that it is hard to execute “use db” to change the database before calling the SP, so I had to use nested dynamic sql (see example) to create the script and make use of both sp_executesql and EXEC() commands. This is not pretty but it works. (Agh, cursors… I hate cursors!)

-- list bases owned by specific login. script to change below.
SELECT name, SUSER_SNAME(owner_sid) owner
FROM   sys.databases
where SUSER_SNAME(owner_sid) = 'user_to_remove'
DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);

declare cbases cursor fast_forward for
 SELECT name
 FROM   sys.databases
 where SUSER_SNAME(owner_sid) = 'user_to_remove'
open cbases
fetch next from cbases into @dbName
while @@FETCH_STATUS = 0
 SET @sql = N'exec sp_changedbowner ''''sa''''';
 SET @BigSQL = N'USE [' + @dbName + ']; EXEC sp_executesql N''' + @sql + '''';
 --print @BigSQL
fetch next from cbases into @dbName
close cbases
deallocate cbases

While some may argue that changing this property (or any other) to “SA” is not the ideal best practice, it is at least more secure than the wrong owner and easier to manage in the short term. After you have an error-free environment, you can start to improve it – but first, it has to work!

For a little more information on the subject please read:

PS1: some errors you may face when there is something wrong with the database owner property:

Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.
Msg 15174, Level 16, State 1, Line 2
Login 'your_login' owns one or more database(s). Change the owner of the database(s) before dropping the login.

PS2: the “map” option is deprecated, don’t care with it. By the way the entire sp_changedbowner is deprecated in SQL 2012, if you have SQL2012 please use ALTER AUTHORIZATION instead (and if you update my script, please send it back to me! =D ) .

About these ads

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in SQLServer and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s