SQL Server check user permissions on database

Do you want to check user permissions in SQL Server? In this SQL server tutorial, I will explain to you how to check user permissions on a database in SQL Server.

I will also explain, how to check user permissions in sql server management studio, sql query to check user permissions on the database, and sql server view permissions for user.

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 the 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 whom you want to assign the permission.
sql server check user permissions on database
  • Now, you can see a list of user permissions. You can check the boxes with respect to the permissions associated to change the permissions explicitly. 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.

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'
sql query to check user permissions on database

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 from a sql server database.

SQL Server check user permissions on database

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 one 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: This 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
    • The database is in single-user mode, and another user is using the database
  • NULL: This 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
sql check user permissions, check user permissions sql server

In this way, you can check a user’s database access in sql server.

Check current user permissions in SQL Server

To check the user permissions in an 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 an SQL Server database, you can execute the script below:

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
how to check user permissions in sql server management studio, check user permissions sql server

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

SQL Server view permissions for user

To check if a user or role has access to a view in sql server, 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 have access to the view. Also, you can see the type of access the user or role has.
sql server view permissions for user

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 have access to the view named SampleView using the script below:

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'
sql view permissions for user

Conclusion

In this SQL server tutorial, we learned how to check user permissions on a database in sql server, check user permissions in sql server, how to check user permissions in sql server management studio, and also sql query to check user permissions on a database. I have also explained, how to check sql server view permissions for users.

You may also like: