SQL Scripts: How To Change The Owner Of A Database

Sometimes it becomes necessary to change the owner of a database.  When a database is first created, SQL Server likes to set the owner of that database to the login of whoever created it.  This also seems to be the case when restoring a database, the owner is set to the login that does the restore.

To see which login has been assigned as owner of your databases, you can query the sys.databases view:

You may be surprised at the account listed as the owner of your databases.  You may also be confused between the differences in the “owner of a database”, the “dbo” user/schema, and the “db_owner” permission role, all of which can sometimes be referred to as “Database Owner”.  The accepted answer in this Stack Exchange post should help to clarify that.

If you find from the query above that you need to change the owner of a database, this can easily be done by running a simple T-SQL statement.

The Old Way: sp_changedbowner

sp_changedbowner has been around since at least SQL Server 2000, although the docs seem to indicate SQL Server 2008.  Using this procedure is pretty straight forward, just execute it on the database you wish to change ownership for, and specify the new owner for that database:

However, as Microsoft states in the sp_changedbowner documentation:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER AUTHORIZATION instead.

The New Way: ALTER AUTHORIZATION

ALTER AUTHORIZATION is the currently preferred way to change the owner of a database.  It was introduced in SQL Server 2005 (although the docs again say 2008) as a replacement for sp_changedbowner, so we really can’t call it the “new” way of doing things since it has been around for over a decade.

As with the previous example, this is a pretty straight forward T-SQL command:

The nice thing about ALTER AUTHORIZATION is that it can be used to change the ownership of several other things, not just a database.  Be sure to review the Microsoft docs for a full list with examples.

This post is part of an ongoing SQL Scripts series, in which I list useful queries that I use in my day to day database administration.

(Visited 63 times, 1 visits today)

Leave a Reply