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.
- T-SQL sp_changedbowner statement
- 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.
- Create a database named EltechDB
- Change the database owner using SQL Server Management Studio
- 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
|
1 2 3 |
use [master] go create database [EltechDB] |
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.
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.
Alternatively, you can see the owner of the EltechDB database by executing the following query.
|
1 2 3 4 |
use [master] go select db.name as [Database Name],sp.name [SQL Login Name] from sys.databases db left join sys.server_principals sp on db.owner_sid=sp.sid where db.name='EltechDB' |
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.
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.
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.
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.
On the Database Properties screen, you can see that the owner of the EltechDB has been changed.
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:
|
1 |
Exec sp_changedbowner [user_name] |
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.
|
1 2 3 4 |
use [EltechDB] go exec sp_changedbowner 'NISARG-PC\Nisarg' go |
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.
|
1 2 3 4 |
use [master] go select db.name as [Database Name],sp.name [SQL Login Name] from sys.databases db left join sys.server_principals sp on db.owner_sid=sp.sid where db.name='EltechDB' |
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.
|
1 2 3 4 5 6 |
use master go select 'use [' + db.name+']; exec sp_changedbowner [sa];' from sys.databases db left join sys.server_principals sp on db.owner_sid=sp.sid where sp.name <>'sa' |
The T-SQL script generated by the query is following:
|
1 2 3 4 5 6 7 8 |
use [AdventureWorks2017]; exec sp_changedbowner [sa]; use [WideWorldImporters]; exec sp_changedbowner [sa]; use [StudentDB]; exec sp_changedbowner [sa]; use [VSDatabase]; exec sp_changedbowner [sa]; use [DBATools]; exec sp_changedbowner [sa]; use [SQLAuthDB]; exec sp_changedbowner [sa]; use [DemoDatabase]; exec sp_changedbowner [sa]; use [EltechDB]; exec sp_changedbowner [sa]; |
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.
|
1 2 3 4 |
use [master] go select db.name as [Database Name],sp.name [SQL Login Name] from sys.databases db left join sys.server_principals sp on db.owner_sid=sp.sid where database_id>5 |
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.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022