Select [Users Permissions]

Question

the database we work on has some standard permissions for new users, but from time to time some users need more than the usual permissions (most of them are EXEC to several SPs). I've been managing these users GRANT. But for admin purposes I'd like to create a personal table with some of that information so I can easily and quickly difference the roles and users info.

Do you know of any system tables where I can find and work with this information?

EDIT:

We're currently working with SQL Server 2008.

Also, consider using roles, etc to consolidate multiple users with the same access requirements. — MatBailie, Jun 22 '12 at 13:28
Yes, with the answer Andomar showed to me I can see the roles. Thanks — Daniel Sh., Jun 22 '12 at 13:31

David Brabant · Accepted Answer · 2012-06-22T13:33:03.487

SELECT
      dp.class_desc, dp.permission_name, dp.state_desc,
      ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name
      FROM sys.database_permissions dp
      JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id
      JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id

For roles:

SELECT
    p.name, p.type_desc, pp.name, pp.type_desc, pp.is_fixed_role
    FROM sys.database_role_members roles
    JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
    JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

Select [Users Permissions]

1 Answers1