Blog

When a database is created, the logged in user is set as the database owner as default. Often, this user account is a physical person and he or she may get more permissions in the database than intended. Normally, a system account should be used as database owner. For instance sa.

Note that when you change the database owner the old account may get less permissions than before, not being the owner (who can do anything) anymore. So check how and where the old account is used before changing.

sp_changedbowner

sp_changedbowner is the system store procedure that traditionally has been use to change the database owner. The syntax is:

EXEC sp_changedbowner @loginame = 'sa';

Run the command in the database you want to change. There is an additional parameter, @map, that has no effect on SQL Server 2008 and forward. In SQL Server 2005 and earlier, any aliases assigned to the old owner could be remapped to the new owner with this parameter. To remap, use parameter @map = true. If you submit @map = false, all aliases mapped to the old owner are dropped. Aliases are not supported in SQL Server 2008 and later, so the parameter has no effect.

sp_changedbowner is depreciated however, but it is still working in SQL Server 2012. The new function to use is ALTER AUTHORIZATION.

ALTER AUTHORIZATION

ALTER AUTHORIZATION is a general function to change the owner on a lot of types in a SQL Server instance. To change the owner on a database, the syntax is:

ALTER AUTHORIZATION ON DATABASE::databasename to sa;

Change the database owner in SSMS

If you want to change the database owner in a user interface, right-click the database and choose Properties. Select the Files page and change the database owner in the Owner text box:

Interestingly, if you script this change you’ll see that SSMS still uses the depreciated sp_changedbowner command in the 2012 version.

Change database owner on a log shipped secondary server

To change the database owner on a secondary database in a log shipping scenario is not an easy task. As far as I know, to do this you must either fail over to the secondary in order to bring it online. Or recreate the log shipping solution from scratch, although logged in with the correct user. Depending on your environment, these may not be very practical solutions however. One way to handle this is to make sure the change of database owner is a part of the fail over routines. And make sure you log in as the intended database owner when you set up log shipping.

EXECUTE AS

The database owner gets perhaps more permissions than a single user should have. Other than that, there are not many dangers of using the wrong database owner. The exception however, being when the EXECUTE AS functionality is used. If a removed account is used as database owner there will be problems with EXECUTE AS. This is apparently the case if service broker is used.