SQL Server User Permissions

In this SQL Server tutorial, we will discuss some of the ways to manage user permissions in a SQL Server database.

In SQL Server, authorization is maintained using permissions at different levels. Various objects in the database can be accessed by some specific users or roles. In this article, we will discuss how these permissions are managed in a SQL Server Database.

• How to check user permissions in SQL Server management studio?
• SQL Server user roles and permissions query
• How to find list of users having access to a table in SQL Server?
• How to check if a user has access to a database in SQL Server?
• Check who has access to SQL Server view
• How to check current user permissions in SQL Server?
• SQL Server get effective permissions for user

How to check user permissions in SQL Server management studio?

SQL Server Management Studio makes it easy to manage permissions because of its graphical user interface. If you want to manage the permissions of an object, follow the below steps:

• Open the object explorer window and navigate to your object whose permissions you want to manage under your database.
• Right click on the object and click on Properties.
• In the Properties dialog box, navigate to the Permissions tab.
• Click on Search to find and select a user to which you want to assign the permission.

• Now you can see a list of user permissions. You can check the boxes with respect to the permissions associated to explicitly change the permissions. Click on OK to save the changes.
• Also, you can switch to the Effective tab to see the list of effective permissions.

Hence, in this way you can manage user permissions using SQL Server management studio in a very convenient manner.

Also, check: SQL Server Port – Details Guide

SQL Server user roles and permissions query

To get information about the users’ or roles’ permissions, you can query the sys.database_principals system catalog view.

For example, you can execute the below T-SQL query to get a list of all the users and roles along with the permissions associated with them on various objects.

SELECT  pri.name As Username
,       pri.type_desc AS [User Type]
,       permit.permission_name AS [Permission]
,       permit.state_desc AS [Permission State]
,       permit.class_desc Class
,       object_name(permit.major_id) AS [Object Name]
FROM    sys.database_principals pri
LEFT JOIN
        sys.database_permissions permit
ON      permit.grantee_principal_id = pri.principal_id

This will be a huge list. You can also customize this query to get the permissions associated with a user or role by adding the WHERE condition.

For example, if I want to get a list of permissions associated with a user named DemoUser, I can execute the below T-SQL query:

SELECT  pri.name As Username
,       pri.type_desc AS [User Type]
,       permit.permission_name AS [Permission]
,       permit.state_desc AS [Permission State]
,       permit.class_desc Class
,       object_name(permit.major_id) AS [Object Name]
FROM    sys.database_principals pri
LEFT JOIN
        sys.database_permissions permit
ON      permit.grantee_principal_id = pri.principal_id
WHERE name = 'DemoUser'

You can see in the above image that a list of user permissions is returned in the resultset.

Hence, in this way, you can get the list of users and roles along with the associated permissions.

Also, read: MySQL vs SQL Server

How to find list of users having access to a table in SQL Server?

There are two methods to find the list of users having access to a table in SQL Server:

Using SQL Server management studio:

Suppose I have created a table in my SQL Server database named dbo.Student. If I want to see the list of users or roles having access to this level, I will follow the below steps in SQL Server management studio.

• In the object explorer window, expand your database and navigate to the table whose permissions you want to see.
• Right click on the table and click on Properties.

• Navigate to the Permissions tab. Here you can see the list of users or roles having access to the table.

• You can also see and manage what type of permissions are granted to a user by clicking on the user name.
• To grant a permission, you can check the box associated with the required permission below the Grant column. Similarly, you can also Deny the permissions of the user. But, you should be authorized enough to manage the permissions.

Now let us see how you can get this information using a T-SQL query.

Using T-SQL query:

You can use a system stored procedure named sp_table_privileges to get the list of users and permissions associated with a table. The general syntax to use this stored procedure is:

sp_table_privileges [ @table_name = ] 'table_name'     
     [ , [ @table_owner = ] 'table_owner' ]   
     [ , [ @table_qualifier = ] 'table_qualifier' ]   
     [ , [ @fUsePattern = ] 'fUsePattern' ]  

For example, let us execute this stored procedure for the table named dbo.Student.

EXEC sp_table_privileges   
   @table_name = 'Student';  

You can see the list of users having access to the table. You can also see the type of access that is given to the user.

Thus, you might have learned how you can view the permissions associated with a table.

Read: Types of Backup in SQL Server

How to check if a user has access to a database in SQL Server?

In SQL Server, you may need to check if a user has access to a database or not. In that case, you can use the HAS_DBACCESS function. For example, If I want to check the database access for the current user, I can use a statement like the below:

PRINT(HAS_DBACCESS('master'))

You have to provide the database name to the function and you will get one of the three values as the result:

• 1: Means the user has access to the database
• 0: Means user does not have access to the database due to one of the following reasons:
  • Database is offline
  • Database is in single-user mode and another user is using the database
• NULL: Means the database name provided is not valid

If you want to check access to the database for another user, you can execute the query using the EXECUTE AS statement. For example, If I want to check the access of a user named DemoUser to the database master, I can use the below query:

EXECUTE AS USER= 'DemoUser'
PRINT(HAS_DBACCESS('master'))
GO

In this way, you can check the database access of a user.

Read: SQL Server Create Temp Table

Check who has access to SQL Server view

To check if a user or role has access to a view, you can use any of the two methods:

Using SQL Server management studio:

• In the object explorer window, right click on the view and click on Properties.
• Navigate to the Permissions tab.
• Here you can see the list of users or roles who has access to the view. Also, you can see the type of access the user or role has.

Using T-SQL query:

You can substitute the name of your view in the below script. For example, I will find the list of users and roles that has access to the view named SampleView using the below script:

SELECT  pri.name As Username
,       pri.type_desc AS [User Type]
,       permit.permission_name AS [Permission]
,       permit.state_desc AS [Permission State]
,       permit.class_desc Class
,       object_name(permit.major_id) AS [Object Name]
FROM    sys.database_principals pri
LEFT JOIN
        sys.database_permissions permit
ON      permit.grantee_principal_id = pri.principal_id
WHERE object_name(permit.major_id) = 'SampleView'

Read: SQL Server INSERT INTO SELECT

How to check current user permissions in SQL Server?

To check the user permissions in a SQL Server instance, you can query the sys.database_permissions and the sys.database_principals system catalog views.

In case, you want to check the current user permissions in a SQL Server database, you can execute the below script:

SELECT all_permissions.permission_name AS [Permission Name], p.name AS [Current User]
FROM (
    SELECT DISTINCT permission_name
    FROM sys.database_permissions
) all_permissions
LEFT JOIN (
    SELECT b.name, a.permission_name
    FROM sys.database_permissions a
    JOIN sys.database_principals b on a.grantee_principal_id = b.principal_id 
    WHERE b.name  = CURRENT_USER
) p ON p.permission_name = all_permissions.permission_name

You can see that the current user is DemoUser. Also, we got the list of permissions associated with this current user.

Read: SQL Server stored procedure parameters

SQL Server get effective permissions for user

To get effective permissions of any securable in SQL Server, you can use the sys.fn_my_permissions system catalog view.

If you want to get effective permissions for a user in SQL Server database, you can use this system catalog view as:

SELECT * FROM fn_my_permissions(<username>, 'USER');  
GO

In the above statement, the username will be the name of the user whose effective permission you want to list. The second argument will be USER as the class of the securable is USER.

For example, If I want to list the effective permissions of the user named DemoUser, I can use the below T-SQL query:

SELECT * FROM fn_my_permissions('DemoUser', 'USER');  
GO

You can see that we got the list of effective permissions associated with the specified user.

Also, take a look at some more tutorials on SQL Server.

• SQL Server First Day Of Month
• SQL Server Drop Trigger If Exists
• Trigger in SQL Server for Insert and Update
• SQL Server logical operators and example
• Comparison Operators in SQL Server
• SQL Server bulk insert from CSV file
• Arithmetic operators in SQL Server

So, in this tutorial, we have learned how to check user permissions in SQL Server. Moreover, we have also covered the following topics.

• How to check user permissions in SQL Server management studio?
• SQL Server user roles and permissions query
• How to find list of users having access to a table in SQL Server
• How to check if a user has access to a database in SQL Server
• Check who has access to SQL Server view
• How to check current user permissions in SQL Server?
• SQL Server get effective permissions for user