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!)
/* http://thelonelydba.wordpress.com -- 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 begin SET @sql = N'exec sp_changedbowner ''''sa'''''; SET @BigSQL = N'USE [' + @dbName + ']; EXEC sp_executesql N''' + @sql + ''''; --print @BigSQL EXEC (@BigSQL) fetch next from cbases into @dbName end 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 ) .