How to get list of users in SQL Server

In this SQL Server tutorial, we will learn How to get list of users, How to get a list of objects available in SQL Server. We will also demonstrate these topics with the help of examples. The complete list of topics discussed in this tutorial is listed below.

• How to get list of users in SQL Server
• How to get list of active connections in SQL Server
• How to get list of orphaned users in SQL Server
• How to get list of roles in SQL Server
• How to get list of user-defined functions in SQL Server
• How to get list of user-defined tables in SQL Server
• How to get list of user-defined views in SQL Server
• How to get list of user-defined types in SQL Server
• How to get list of user-defined stored procedures in SQL Server
• How to get list of permissions in SQL Server
• How to get list of logins in SQL Server

How to get list of users in SQL Server

In this section, we will understand how to fetch all the users in a SQL Server database.

Now, whenever we work on a SQL Server, there are two ways to interact with the database. The first is a GUI option by using SQL Server Management Studio, and the second is by executing Transact-SQL queries. And in this section, we will discuss both ways by illustrating some examples.

Using Transact-SQL

Now, when we are using a Transact-SQL query to get the list of users, there are multiple ways to list all the users. But, we will be discussing a standard way that we can use to list users. For this implementation, we will be using sys.database_principals view.

The sys.database_principals is a system catalog view available in SQL Server, and we can easily query this view to list all the users created in the database. Here is the query that we can use to list the users in a particular 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'

In the above code, we simply need to specify the database name in place of database_name. And this query will return all the users available in that database.

The example related to this execution is shown below.

Using SQL Server Management Studio

We can also use SQL Server Management Studio to get a list of users. But the list will also contain roles and windows groups. To display the list we have to follow the given steps.

• First, move to “Object Explorer” and expand the database that you want.
• Next, under the database, expand the “Security” directory.
• Now, under Security, expand the “Users” option. This will display a list that contains all the users created in that database.

Read: What is a stored procedure in sql server

How to get list of logins in SQL Server

In this section, we will understand how to fetch all the logins available in a SQL Server instance.

Again we are going to execute this task using 2 methods. The first is by using Transact-SQL query, and the second is by using SQL Server Management Studio.

Using Transact-SQL

For this implementation, we will be using the sys.server_principals catalog view. So, to list all the logins available in a SQL Server, we will query the sys.server_principals view.

The sys.database_principals is a system catalog view available in SQL Server, and we can easily query this view to list all the logins created in an instance. The query that we can use to list the logins is given below.

SELECT name AS [Login Name], type_desc AS [Account Type]
FROM sys.server_principals 
WHERE TYPE IN ('U', 'S', 'G')
and name not like '%##%'
ORDER BY name, type_desc

In the above code, we are querying the sys.server_principals to select the name and type column. And we are also using the WHERE clause to filter the result based upon 3 types of logins. The ‘U‘ is for Windows login, ‘S‘ is for SQL login, and ‘G‘ is for Windows group login.

An example of this implementation is given below.

Using SQL Server Management Studio

We can also use SQL Server Management Studio to retrieve a list of logins. Anf for this, we have to follow the given steps.

• First, move to “Object Explorer” and expand the server instance.
• Next, under server, expand the “Security” directory.
• Now, under Security, expand the “Logins” option. This will display a list that contains all the logins created in the particular instance.

Read: Loop in SQL Server stored procedure

How to get list of roles in SQL Server

In this section, we will understand how to fetch all the roles available in a SQL Server database.

Now, there are two ways to list the roles in the SQL Server database.

1. Using Transact-SQL
2. Using SQL Server Management Studio

Using Transact-SQL

Now, to fetch a list of roles available in a database, we will be using sys.database_principals catalog view. This catalog view contains data of each security principal in a SQL Server database including database roles. The query to get all roles in a database is as follows.

SELECT [name], [create_date], [modify_date] 
FROM sys.database_principals
WHERE type = 'R'
ORDER BY [name]

In the above code, we are querying the sys.database_principals view to fetch 3 columns. And we are also using the WHERE clause to get all the Database roles.

In sys.database_principals type ‘R’ is used for database roles. Additionally, we can also get all application roles by replacing ‘A’ with ‘R’.

For example, consider the following query and result.

Using SQL Server Management Studio

We can also use SQL Server Management Studio to get a list of roles. For this, we have to follow the following given steps.

• First, move to “Object Explorer” in the SQL Server Management and expand the database that you want.
• Next, under the database, expand the “Security” directory.
• Now, under Security, expand the “Roles” option. Now, we have two options, either expand “Database Role” or expand “Application Roles” according to your needs.

Read: Stored procedure for search functionality in SQL Server

How to get list of user-defined functions in SQL Server

In this section, we will discuss How we can fetch a list of all different types of user-defined functions created in a database.

For this implementation, we will be using the sys.objects table. The sys.objects is a system table in SQL Server that stores information of each object created within a database.

These objects include stored procedures, functions, views, etc. And we will query this stable to list all user-defined functions available in a database. For this, we will execute the following query.

USE database_name
GO

SELECT name AS [Function Name],
       SCHEMA_NAME(schema_id) AS schema_name,
       type_desc
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO

To execute this given query, we simply need to provide the name of the database for which we want to list the functions. And we have to specify the database name in place of database_name.

After execution, the query will return all the user-defined functions created in that particular database.

For better understanding, consider the following example and result.

Read: SQL Server stored procedure vs function

How to get list of user-defined views in SQL Server

In this section, we will discuss How we can fetch a list of user-defined views created within a database.

Now, there can be many different methods to get a list of user-defined views in SQL Server. In this section, we will discuss a standard approach that can be used both in SQL Server Management Studio as well as command prompt.

Again, for the implementation of this, we will use sys.objects system table. And we will implement the following query to get the result.

USE database_name
GO

SELECT name AS [View Name],
       SCHEMA_NAME(schema_id) AS schema_name,
       type_desc
FROM sys.objects
WHERE type = 'V';
GO

In the above code, we only need to replace your database name with the database_name string. Here is an example of this execution.

Read: SQL Server stored procedure if else

How to get list of user-defined types in SQL Server

In this section, we will discuss how we can fetch a list of user-defined types created within a database.

Till now, we were using the sys.objects system table but it does not contain any data related to user-defined types. So, now we have to use the sys.types system table.

The sys.types is also a system table and it carries information related to column data types. So, now we will query the sys.types table to fetch a list of user-defined types. For this implementation, we will execute the following query in the query editor.

USE database_name
GO

SELECT * FROM sys.types
WHERE is_user_defined = 1
GO

In the above code, we are querying the sys.types table to fetch all of its columns. After this, we are using a WHERE clause to filter all the user-defined types from the database. For demonstration, consider the following example with the output.

Read: How to view stored procedure in SQL Server

How to get list of user-defined tables in SQL Server

In this section, we will discuss how we can fetch a list of user-defined tables created within a database.

In SQL Server, there are multiple ways to get a list of user-defined tables, and we will try to demonstrate most of them in this section. Most commonly, we can use 2 system tables available in SQL Server that stores data regarding user-defined tables.

1. sys.objects
2. sys.tables

Using sys.objects

As explained earlier, sys.objects is a system table in SQL Server that stores rows related to objects created within the database. So, we need to query this table and filter all the user-defined tables from it.

For this, the sys.objects have a column with the name “type“, and different objects are assigned some character that represents that object. For the user-defined tables, that type character is “U“.

Here is the syntax that we can use to get the desired result.

USE database_name
GO

SELECT name AS [Table Name],
       SCHEMA_NAME(schema_id) AS schema_name,
       type_desc
FROM sys.objects
WHERE type = 'U';
GO

Let’s understand the implementation with the help of an example. An example with its output is shown below.

Using sys.tables

The sys.tables is another system table available in SQL Server and it stores data related to tables. So, we simply need to query the sys.tables table to get the required result. The syntax of this execution is as follows.

USE database_name
GO

SELECT name AS [Table Name],
       SCHEMA_NAME(schema_id) AS schema_name,
       type_desc
FROM sys.tables
GO

The execution of an example with its output is shown below.

Read: Types of Backup in SQL Server

How to get list of user-defined stored procedures in SQL Server

In this section, we will discuss how we can fetch a list of user-defined stored procedures from a database.

In SQL Server, there are multiple ways to get a list of user-defined procedures. And we will illustrate the 2 most common queries to implement this task. These queries include the use of the following 2 system tables.

1. sys.objects
2. sys.procedures

Using sys.objects

Here is the query that uses the sys.objects table to list all stored procedures created in a database.

USE database_name
GO

SELECT name AS [Procedure Name],
       SCHEMA_NAME(schema_id) AS schema_name,
       type_desc
FROM sys.objects
WHERE type = 'P'
GO

To use the above query, we need to replace the database name with the database_name. The example of this query is illustrated below.

In the above example, we are listing all the stored procedures created within the sqlserverguides database.

Using sys.procedures

The sys.procedures is another view available in SQL Server that stores data related to objects which are procedures in some way. Here is the query that uses the sys.procedures view to list all stored procedures created in a particular database.

USE database_name
GO

SELECT name AS [Procedure Name],
       SCHEMA_NAME(schema_id) AS schema_name,
       type_desc
FROM sys.procedures
GO

The example of this query is demonstrated below.

Read: Identity Column in SQL Server

How to get list of permissions in SQL Server

Permissions are the various levels of access provided to specified security resources. Permissions are assigned to SQL Server logins and server roles at the server level. They are assigned to database users and database roles at the database level.

In this section, we will discuss how we can fetch a list of built-in permissions available in SQL Server.

For this implementation, we will use the sys.fn_my_permissions system function. This function returns the set of permissions granted to a principal on a securable. Here is a query that we need to execute to get the list of all built-in permissions.

SELECT * FROM sys.fn_builtin_permissions(DEFAULT); 

For example, consider the following screenshot given below.

How to get list of active connections in SQL Server

Now, in this section, we will discuss how to fetch a list of active connections to a SQL Server database.

Now, to get the list of active connections, we will use sys.processes system view. The sys.processes view stores the data related to processes that are running on the SQL Server instance.

And these processes include both systems as well as client processes. The query that we can use to list the active connection is given below.

SELECT DB_NAME(dbid) AS [DB Name],
       COUNT(dbid) AS [Number Of Connections],
       loginame AS [Login Name]
FROM sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)

The example of implementing the above is also given below with its output.

Read: SQL Server Create Temp Table

How to get list of orphaned users in SQL Server

In this section, we will discuss how we can fetch a list of orphaned users in SQL Server. But before that, let’s first understand what are orphaned users in SQL Server.

An orphaned user in SQL Server is the one that is available at the database level but its mapped login is not there at the server level. These types of users are generally created when a database is restored from a backup from one server on another server.

Next, let’s understand how we can list these orphaned users in SQL Server. For this, let’s assume that we have restored a database with the name myDB from server1 to server2.

Now, to list all the orphaned users, we have to execute the following command on server2.

USE myDB
GO

EXEC sp_change_users_login report
GO

After executing the above query, we will get a resultset with 2 columns. The first is the UserName and the second is the UserSID column.

You may also like reading the following:

• IDENTITY_INSERT in SQL Server
• How to check if SQL Server is running
• SQL Server scheduled stored procedure
• How to execute stored procedure in SQL Server

So, in this tutorial, we have learned How to get list of users, How to get list of permissions. And we have also discussed the following list of topics.

• How to get list of users in SQL Server
• How to get list of active connections in SQL Server
• How to get list of orphaned users in SQL Server
• How to get list of roles in SQL Server
• How to get list of user-defined functions in SQL Server
• How to get list of user-defined tables in SQL Server
• How to get list of user-defined views in SQL Server
• How to get list of user-defined types in SQL Server
• How to get list of user-defined stored procedures in SQL Server
• How to get list of permissions in SQL Server
• How to get list of logins in SQL Server