CODEWITHSUNDEEP
sqlsql-server-2008
Daniel Sh.
Daniel Sh.

Reputation: 2074

Select [Users Permissions]

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.

Upvotes: 0

Views: 448

Answers (1)

David Brabant
David Brabant

Reputation: 43619

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

Upvotes: 1

Related Questions