How to Get a List of Users in SQL Server Database | 2 Methods Detailed

A SQL database, often known as a relational database, is a collection of highly structured tables, with each row representing a data object and each column defining a specific information field. 

To view the users in an SQL database, first log in as an administrative user to your SQL server using the SQL command line client, then perform the following SQL query: sql> select * from sql.user. However, keep in mind that this query displays all of the data from SQL. Now let’s get to know more about this topic.

Methods I Followed to Get a List of Users in SQL Server Database

The INFORMATION_SCHEMA views are the simplest way to find all tables of users in SQL. This is accomplished by first describing the information structure, followed by the “tables” view. In this section, we’ll go through two approaches with several instances.

1. Using Transact-SQL

A SQL transaction is a series of SQL statements that interact with a database. A transaction can be committed to a database as a single logical unit or rolled back (undone) as a single logical unit. Transactions are required in SQL to preserve database integrity.

Here is a query that you can use to list the users in a specific database.

USE database_name
SELECT name as username, create_date,
modify_date, type_desc as type
FROM sys.database_principals
WHERE type not in ('A', 'G', 'R', 'X')
and sid is not null
and name != 'guest'

Figure 1: Output of the query

2. Using SQL Server Management Studio

SQL Server Management Studio (SSMS) is a client tool for connecting to and working with our SQL Server through a graphical interface rather than the command line. Microsoft SQL Server 2005 introduced the management studio, which allows you to deal with SQL Server and Azure SQL databases.

It enables DBAs and database developers to configure, manage, and administer all SQL Server components. Its primary functions include the creation of databases and tables, and the execution of SQL queries for inserting, updating, and removing data. To display the list of users, we must follow the instructions.

1. First, go to “Object Explorer” and expand the desired database.
2. Expand the “Security” directory under the database.
3. Expand the “Users” option under Security. This will give a list of all the users that have been created in that database.

Figure 2: Output for the steps performed above

People Also Asked For

How Do I List Only the Enabled Users in SQL Server?

You can use the following Transact-SQL query to list only the enabled users in SQL Server:

SELECT name
FROM sys.database_principals
WHERE type = 'U'
AND is_disabled = 0

How Can I Export the List of Users to a File or Another Database Table?

You can use transact SQL, SSMS, or a third-party tool(ApexSQL Data Export, Red Gate SQL Compare, Toad for SQL Server) to export the list of users to a file or another database table.

1. Transact SQL query:

SELECT name
INTO [file_path]
FROM sys.database_principals
WHERE type = 'U'

2. SSMS SQL query:

SELECT name
FROM sys.database_principals
WHERE type = 'U'

What SQL Server Permissions Are Required to Query for a List of Users?

The following permissions in SQL Server  are needed to query for a list of users:

1. View Server State: Users with the View Server State Permission can execute and view results for server-scoped database management views functions. For example, if you run the DMV sys. dm_os_wait_stats to check wait statistics in SQL Server. 
2. View Database State: Allows you to view the database’s conditions using database-level dynamic management views or functions. Select – Allows you to utilize the SELECT command on all relevant objects in the database.

If you simply wish to query a certain database for a list of users, you only need the VIEW DATABASE STATE permission for that database.

Conclusion

You may need to administer a database in MySQL at times. In that instance, we require access to a list of all user accounts in a database. Most of the time, we presume that there is a SHOW USERS command, similar to SHOW DATABASES, SHOW TABLES, and so on, that displays a list of all users on the database server.