Nisarg Upadhyay
Database Owner

Different ways to change database owners in SQL Server

May 17, 2021 by

In this article, we will explore different ways in SQL Server to change the database owner. We can use any of the following methods in SQL Server to change the database owner.

  1. T-SQL sp_changedbowner statement
  2. Database property settings in SQL Server Management Studio

To understand the concept more clearly, we are going to prepare a demo setup and perform the following activities.

  1. Create a database named EltechDB
  2. Change the database owner using SQL Server Management Studio
  3. Change the database owner using a T-SQL statement

Additionally, I will show a script used in SQL Server to change the database owner of several user databases.

First, let us create a database. To create a database, run the following query

When we create a new database, the user who has created a database becomes the owner of the database. In our case, the owner of the database is Nisarg-PC\Nisarg because we have connected to the SQL Server using Nisarg-PC\Nisarg login.

We can view the database owner from SQL Server Management Studio. To do that, right-click on EltechDB and click on Properties.

View database property

On the General screen of the Database Properties dialog box, you can see the Owner of the database in the Owner row. See the following image to see the exact location.

Database Owner

Alternatively, you can see the owner of the EltechDB database by executing the following query.

Query to view the database owner

As you can see in the above query output, the database owner of the EltechDB is Nisarg-PC\Nisarg login.

Change the database owner using SQL Server Management Studio (SSMS)

First, let us understand the process of changing the database owner using SQL Server Management Studio. To do that, open database property as explained above. In the Database Properties dialog box, click on Files. Click on the […] icon, which is next to the Owner text box.

View list of users

A dialog box Select Database Owner opens. In the Select Database Owner dialog box, you can specify the user name in Enter the object names to select the dialog box. If you are not sure about which SQL Login to choose, you can click on Browse.

Locate the existing users

Another dialog box Browse for Object Opens. You can choose the desired user from the list. In our case, we are selecting sa user, so select sa from the list and click on OK.

select sa account

Back to the Select database owner screen, the sa username is added to the list. Click OK to save the changes and close the dialog box.

sa account has been chosen

On the Database Properties screen, you can see that the owner of the EltechDB has been changed.

Method1: SQL Server change database owner using ssms

Click OK to save the changes and close the dialog box. Now, let us understand another approach in SQL Server to change the database owner.

Change the database owner using sp_changedbowner

We can use the system stored procedure named sp_changedbowner to change the database owner. The syntax of the sp_changedbowner is following:

In the syntax, the value of the user_name parameter is the SQL login that you want to use as the database owner. In our case, we are changing the database owner from sa to Nisarg-PC\Nisarg. To do that, execute the following query.

To populate the database name and its owner, you must design a query that uses INNER JOIN to join sys.database and sys.server_principals DMVs and retrieve the database name and user name.

Current database owner

Recently, I was working with a client, and I observed that the owner of several databases is the domain accounts. As a SQL Server best practice, the owner of any user database must be sa. Therefore, we decided to change the database owner to sa login.

The client has 50 user databases, so changing the database owner using SQL Server management studio was not feasible. I would have used the sp_changedbowner stored procedure. However, in that approach, I must manually copy and paste the database name in the stored procedure, so I had created a script that iterates through all user databases and creates a dynamic T-SQL command that is used to change the database owner. Below is the query.

The T-SQL script generated by the query is following:

Execute the above query in our SQL Server instance and see how it changes the database owner to sa login. Once the command executes successfully, run the below query to verify that the database owners have been changed.

login has been changed for all databases

As you can see, the database owner has been changed to sa.

Summary

In this article, we explored different methods in SQL Server to change the database owner. I have explained two approaches. In the first approach, we learned how we could change the database owner using the SQL Server Management Studio. In the second approach, we learned how to use the system stored procedure sp_changedbowner in SQL Server to change the database owner. I have also shown a T-SQL script that generates a dynamic query that changes the database owner to sa login.

Nisarg Upadhyay
826 Views