How to Change Database Owner in SQL Server?

In this SQL Server tutorial, you will learn how to change database owner in SQL Server.

Where you will learn about ‘What are reasons behind changing the ownership of database?’. Then, you will understand how to use SSMS to change ownership of the database in SQL Server.

At the end, you will learn about the ALTER AUTHORIZATION statement in SQL Server with syntax to change the owner of the databases.

Change Database Owner in SQL Server

Before proceeding to change the database owner in the SQL Server, Let’s know ‘Why is there a need to change the database owner?’.

There are several reasons, which are shown below.

• You know that the database is managed by the user who works in a company; if the employee leaves a company, then there is a need to change the database owner to a new owner.
• Sometimes, you need better control and security for your database; then, you can assign ownership of the database to a particular user or role.
• When databases are migrated from one server to another in that case you need to change the owner of the database to a new owner. Here, ownership should be related to the new user who is going to manage the database.
• Whenever an issue such as an owner or permission conflict occurs, in that case, it is necessary to change the ownership of the database to resolve that issue.

These are some reasons, but in real life, there can be other reasons for changing the ownership of the database.

Here, you will learn two ways to change the owner of the database in SQL Server.

Change Database Owner in SQL Server Using SSMS

Before changing the ownership of the database, you must know the new user or role to whom you are going to assign the ownership.

To know more about the users in SSMS, go to Object Explorer, expand the node Security, and then expand the subnode Logins. In the Logins section, you see the list of users, as shown below.

In the above picture, under Logins, you can see the different users, such as James, MSI\saura, etc.

So here, identify the user to whom you want to assign the ownership of the database; in this case, I am choosing user James.

Now, next, assign the ownership of the database to James. For that, expand the Database node of your Object Explorer panel. After that, choose the database, here choosing the database ‘Customer’ whose ownership will be transferred to the user James.

Choose the database ‘Customer’ and right-click on it, then select the option Properties as shown in the above picture. After that, the Database Properties dialog box appears, as shown in the picture below.

Following as shown in the above picture, click on the Files page under the Select a page section on the left side. Then you see the name of the database as ‘Customer’ and the owner as ‘MSI\saura’.

To change the ownership to the user, James. click on the three-dot after the owner input field.

Then a Select Database Owner dialog box appears; click on the Browse button to choose the owner for the database, as shown in the picture below.

When you click the Browse button, a new dialog box named Browse for objects appears.

Now choose the owner, James, as shown in the above picture, but in your case owner may be different. After selecting the owner, click on the OK button.

To check the ownership of the database, again right-click on the ‘Customer’ database. Click on the General page under the Select a page section, as shown in the picture below.

You can see the database ‘Customer’ owner changed to James under the Database section. This is how to change the database owner in the SQL Server Management Studio.

Sometimes, you don’t have access to the SSMS; in that case, you can change the database owner using the SQL statement. In the next section, you will understand how to change the database owner in SQL Server using the SQL statement.

Change Database Owner in SQL Server Using Alter Authorization

SQL Server provides a statement called ALTER AUTHORIZATION; using this statement, you can alter the owner of the database in SQL Server.

The syntax is given below.

ALTER AUTHORIZATION ON DATABASE:: database_name TO database_newowner

Where,

• ALTER AUTHORIZATION: This is the command itself to modify the owner of the database or change the database owner in the SQL Server.
• DATABASE:: database_name: In place of database_name, specify the name of the database whose owner you want to change.
• TO database_newowner: In place of database_newowner, specify the name of the new owner for the database.

First, open the SQLCMD and connect to the SQL Server instance using the below command in your command line.

SELECT name FROM sys.databases;
GO

The query lists all the databases on the current SQL Server instance, as shown in the below picture.

You can see all the databases, such as master, model, Product_Orders, etc. Now, suppose you want to change the ownership of the Product_Orders database.

But first, check the current owner of the Product_Orders database using the below query.

SELECT SUSER_SNAME(owner_sid) AS CurrentOwner FROM sys.databases WHERE name = 'Product_Orders';
GO

The owner of the Product_Orders database is MSI\saura, so here you will see how to change the database owner in SQL Server to a new owner named James.

So write the below query in your command line and execute it to change the database owner in SQL Server from MSI\saura to James.

ALTER AUTHORIZATION ON DATABASE::Product_Orders TO James;
GO

Now run the below query to verify that ownership has been transferred successfully.

SELECT SUSER_SNAME(owner_sid) AS CurrentOwner FROM sys.databases WHERE name = 'Product_Orders';
GO

From the output, you can see that the ownership of Product_Orders changed from MSI\saura to James.

Using the SQL Server Management Studio and Alter Authorization statement, you can change the ownership of any database in SQL Server to any new owner. These are the two ways to change the database owner in SQL Server.

Conclusion

In this SQL Server tutorial, you learned how to change the ownership of the existing database in an SQL server. Then changed the ownership of the database using the SSMS, after that you learned how to change ownership of the database using the Alter Authorization statement. Also learned how to check the owner of the database.

You may like the following tutorials:

• Get Connection String in SQL Server Management Studio
• How to Change Database Name in SQL Server using Query?
• How To Find Database Owner in SQL Server?

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.