In SQL Server, each database has an associated owner, which is a principal that has certain permissions and control over the database. The database owner is a user or login that has been granted the dbo (database owner) user role for that database. The database owner has the highest level of permissions within the database and can perform administrative tasks such as creating or modifying objects, managing security, and altering the database schema.

Changing the owner of a database in SQL Server can be done for several reasons:

• Security: By changing the database owner, you can ensure that the ownership and associated permissions are correctly assigned to the appropriate user or login. This helps in maintaining a secure environment by controlling access to the database.
• Database Maintenance: Sometimes, when a user or login that owns a database is removed or becomes inactive, it may be necessary to transfer the ownership to another active user or login. This ensures that the database remains manageable and can be properly maintained.
• Compatibility: Changing the database owner can be necessary when migrating a database from one server to another. The ownership must be updated to match the new server's login or user.
• Troubleshooting: In certain situations, changing the database owner can resolve issues related to permissions or ownership conflicts. It can help resolve problems with accessing or managing the database.

By changing the database owner, you can maintain proper control, security, and administration of the database, ensuring that an active and authorized user or login owns it.

The database owner is set during the database creation or can be changed later using the ALTER AUTHORIZATION statement or sp_changedbowner system stored procedure. By default, when a new database is created, the user who creates the database becomes its owner. However, you can explicitly specify a different user or log in as the owner during the creation process.

In this article, we will explore how we can change the database owner. For demonstration, I have restored the wideworldimporters database.

When we create or restore a new database, the user who has created or restored, it becomes the owner of the database. You can view the database owner by using the following query

use master 
go 
select name[Database Name],SUSER_SNAME(owner_sid)[Database Owner], create_date [Database Create Date] from sys.databases 

The query retrieves the name of the database (name column) and converts the owner_sid to a login or username using the SUSER_SNAME function, which gives you the database owner.

The query output shows the name of the owner of the wideworldimporters database is ‘DC0\Administrator’. We want to change it to DC0\n.upadhyay.

Modifying database ownership using SSMS

Open SSMS and connect to database engine, right-click on Wideworldimportors, and select Properties. In the Database Properties dialog box, select Files, and Click on the balloon icon.

In the Select Database Owner dialog box, specify the user name in Enter object names to select the textbox, or you can search for the desired user name. To select the user name, click Browse.  Another dialog box named Browse for Object Opens. Here you can select the desired user from the list. Note that you can view only those SQL logins that already exist in the SQL Server instance.

In our case, we are selecting DC0\n.upadhyay user, so select DC0\n.upadhyay from the list and click Ok.

The selected username will added in the dialog box. Click Ok. To verify the changes, execute the following query in SQL Server management studio or dbForge Studio for SQL Server.

use master 
go 
select name[Database Name],SUSER_SNAME(owner_sid)[Database Owner], create_date [Database Create Date] from sys.databases

Modifying database ownership using sp_changedbowner and ALTER AUTHORIZATION

We can use the sp_changedbowner to change the database owner.

Syntax

Exec sp_changedbowner [user_name]

In the syntax, the user_name parameter is the SQL login you want to use as a new database owner.

Example: The following query uses sp_changedbowner to change the owner of the wideworldexporter database from DC0\n.upadhyay to DC0\n.vyas.

use [WideWorldImporters] 
go 
exec sp_changedbowner 'DC0\n.vyas' 
Go

Note that the sp_changedbowner stored procedure is deprecated. So instead of using it, we can use the ALTER AUTHORIZATION SQL statement to change the database owner.

The syntax for changing the database owner using ALTER AUTHORIZATION statement is below

ALTER AUTHORIZATION ON DATABASE::<database_name> TO <new_owner>;

Syntax

In the above syntax,

1. database_name: Specify the name of the database whose owner we want to change.
2. new_owner: specify the new SQL Server login or database user.

Example: For example, you want to change the owner of the wideworldimporters database from DC0\n.vyas to DC0\Administrator. To do that, execute the following query:

ALTER AUTHORIZATION ON DATABASE::WideWorldImporters TO [DC0\Administrator];

Verify the changes by running the following query:

use master 
go 
select name[Database Name],SUSER_SNAME(owner_sid)[Database Owner], create_date [Database Create Date] from sys.databases

Screenshot:

The above screenshot shows database owner has been changed to DC0\Administrator.

Summary

This article explains about database owner and why we need to change it. We understood two methods to change the database owner. In the first method, we learned how to change the database owner by changing the database properties in SQL Server management studio. In the second method, we learned how to use the sp_changedbowner procedure and ALTER AUTHORIZATION statement.