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:
|
1 2 3 4 |
SELECT [name], SUSER_SNAME(owner_sid) FROM sys.databases |
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:
|
1 2 3 4 5 6 |
USE YourDatabase GO --change YourDatabase owner to 'sa' EXEC sp_changedbowner 'sa'; |
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:
|
1 2 3 4 |
--change YourDatabase owner to 'sa' ALTER AUTHORIZATION ON DATABASE::YourDatabase to sa; |
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.
HI Eric. Thank you for the information. It seems straight forward. I do have a question, what about if we need to change the ownership of the dbo NOT to “sa” but to another login use? what will be the correct syntax to declare the login user name?
ALTER AUTHORIZATION ON DATABASE::YourDatabase to sa
Will it be:
ALTER AUTHORIZATION ON DATABASE::YourDatabase to domain\username
You may ask, why would I not want to use “sa”? Well, I inherit the database and I have no clue what the password may be.
Please let me know.
Thank you in advance.
Francisco
Hi Francisco,
You are correct on the syntax. You’ll need to put domain\username in either brackets [domain\username] or quotes ‘domain\username’, I don’t remember which.