Query Current Users and Roles

by

Role Assignment

SELECT u.name, r.name
  FROM sys.database_role_members AS m
  INNER JOIN sys.database_principals AS r
  ON m.role_principal_id = r.principal_id
  INNER JOIN sys.database_principals AS u
  ON u.principal_id = m.member_principal_id

Order By u.name

Role Permissions

SELECT DISTINCT rp.name, 
                ObjectType = rp.type_desc, 
                PermissionType = pm.class_desc, 
                pm.permission_name, 
                pm.state_desc, 
                ObjectType = CASE 
                               WHEN obj.type_desc IS NULL 
                                     OR obj.type_desc = 'SYSTEM_TABLE' THEN 
                               pm.class_desc 
                               ELSE obj.type_desc 
                             END, 
                s.Name as SchemaName,
                [ObjectName] = Isnull(ss.name, Object_name(pm.major_id)) 
FROM   sys.database_principals rp 
       INNER JOIN sys.database_permissions pm 
               ON pm.grantee_principal_id = rp.principal_id 
       LEFT JOIN sys.schemas ss 
              ON pm.major_id = ss.schema_id 
       LEFT JOIN sys.objects obj 
              ON pm.[major_id] = obj.[object_id] 
       LEFT JOIN sys.schemas s
              ON s.schema_id = obj.schema_id
WHERE  rp.type_desc = 'DATABASE_ROLE' 
       AND pm.class_desc <> 'DATABASE' 

       AND rp.name <> 'public'
ORDER  BY rp.name, 
          rp.type_desc, 
          pm.class_desc

Bulk Credentials

SELECT dp.name AS PrincipalName, 
       dp.type_desc AS PrincipalType, 
       dpp.permission_name, 
       dpp.state_desc, 
       dpp.class_desc, 
       dsc.name AS CredentialName
FROM sys.database_principals dp
JOIN sys.database_permissions dpp 
    ON dp.principal_id = dpp.grantee_principal_id
JOIN sys.database_scoped_credentials dsc
    ON dpp.major_id = dsc.credential_id
WHERE dp.name = 'DWH Dev Users'
AND dpp.class_desc = 'DATABASE_SCOPED_CREDENTIAL';

Leave a Comment