Viewing the roles and privileges for a user or role (SQL)

View the roles and privileges a user or has, including roles and privileges they are inheriting.

Prerequisites

sp_displayroles system procedure: No privileges are required to execute this procedure on yourself. However, to return the system privileges or roles for another user ID or a role, you must have the MANAGE ROLES system privilege.
sp_objectpermission system privilege: No privileges are required to execute this procedure on yourself or on objects you own. However, to call this procedure on another user ID, or on an object owned by another user ID, you must have the MANAGE ANY OBJECT PRIVILEGE system privilege.

Context

You can also use this task to view the roles and privileges for a given role.

Procedure

Connect to the database
To view the roles and system privileges the user has, execute a statement that calls the sp_displayroles system procedure, similar to the following, where userid is the user ID of the user:
```
SELECT * FROM sp_displayroles( 'userid', 'expand_down');
```
To view the object-level privileges a user has, execute a statement that calls the sp_objectpermission system procedure, similar to the following:
```
SELECT * FROM sp_objectpermission('userid');
```

Results

The role_name column in the results for sp_displayroles includes inherited roles and system privileges as well as those explicitly granted to the user. If the role or system privilege is inherited from another role, the name of that role is indicated in the parent_role_name column. The grant_type column tells you if the user has administrative rights on the role or system privilege. The role_level column conveys a hierarchy for the inheritance, since inheritance can occur by being member of a role that is a member of another role, and so on. This can help you troubleshoot when you revoke a role or privilege from a user but find they are still able to use the role or privilege.

The results for sp_objectpermission includes inherited object-level privileges as well as privileges explicitly granted to the user. Use the grantee column to learn where the object-level privilege is inherited from. The grantor column tells you who performed the actual granting. The grantable column tells you whether the user has administrative rights on the privilege.