Wednesday, December 4, 2013

How to retrieve all permissions granted to a particular user

Below is the query that will give you all the permissions granted to a particular user for the current database:
SELECT class_desc [Permission Level], CASE WHEN class = 0 THEN DB_NAME()
         WHEN class = 1 THEN OBJECT_NAME(major_id)
         WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable]
  , USER_NAME(grantee_principal_id) [User]
  , permission_name [Permission]
  , state_desc
FROM sys.database_permissions
WHERE USER_NAME(grantee_principal_id)='user1'

No comments:

Post a Comment