In this SQL Server tutorial, you will learn how to find database owner in SQL Server. You will understand the method you use to view the owner of the database in SQL Server.
You learn how to find the database owner using SSMS and Query in your command line.
Find Database Owner in SQL Server
You have different ways to find database owner in SQL Server. Here, you will understand the most commonly used method to check the owner of the database in SQL Server.
You will use the two different methods based on your particular requirement or the information you need.
Find Database Owner in SQL Server: using SSMS
You can find the ownership of the database in SQL Server using the SQL Server Management Studio. So follow the below steps:
Open the SSMS and connect to the SQL Server Instance as shown in the below picture.
After connecting to the SQL Server instance, go to the Object Explorer panel on the left side. Expand the Database node, and you will see the list of databases.
The Database node contains all the databases on the SQL Server, as you can see in the above picture.
To find the owner of any of the databases, simply select the database, such as PRODUCTS, and right-click on it. Then select the option Properties as shown in the below picture.
After selecting Properties, a Database Properties dialog box appears. Then click on the General page under the Select a page section on the left side of the dialog box.
In the above picture, under the Database section, you can see the owner of the PRODUCTS database is MSI\saura. This is how to find database owners in SQL Server using SQL Server Management Studio.
Find Database Owner in SQL Server: using Queries
In the above section, you learned how to find the database owner in SQL Server using the SSMS. Here you will understand how to find the owner of the database using T-SQL queries.
T-SQL, or you can call Transact-SQL, is the primary language provided by Microsoft to manage the SQL Server databases.
Now open your command line or terminal and execute the below command to connect to the SQL Server instance.
sqlcmd -S MSI\SQLEXPRESS
To view the owner of any databases, use the below syntax.
SELECT SUSER_SNAME(owner_sid) FROM sys.databases WHERE name = 'database_name';
In the above syntax, in place of the database_name, write the name of the database whose owner you want to view. When you execute the above query, it returns the database owner specified within the WHERE clause.
Or if you want to find the owner of all the databases on the SQL Server instance, use the below query.
SELECT name, SUSER_SNAME(owner_sid) FROM sys.databases;
- name: This is the column in view sys.databases that contains the names of all the databases in SQL Server.
- SUSER_SNAME(owner_sid): It returns the names of the owners of the databases. The function SUSER_SANME() converts the SID (Security Identifier) of the database owner into a human-readable user name.
- sys.databases: It is the view in SQL Server that stores information about all the databases.
First, find the owner of the specific database using the below query.
SELECT SUSER_SNAME(owner_sid) FROM sys.databases WHERE name = 'Product_Orders'; GO
The query returns the owner of the database Product_Orders; the owner is the MSI\saura. Using the above query, you can find the owner of a specific database.
Next, find the owner of all the databases on the SQL Server instance; for that, run the below query.
SELECT name, SUSER_SNAME(owner_sid) FROM sys.databases; GO
The above query shows the owner of all the databases that you can see in the above output. For example, the owner of the Customer database is James.
This is how to find the database owner in SQL Server using the SSMS and Queries.
In this SQL Server tutorial, you learned how to find the database owner in SQL Server. you learned about two methods: the first method used the SQL Server Management Studio, and the second used the command line.
You may also like the following tutorials:
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.