View database roles, logins, users and permissions

PROBLEM:
How do I list all my database roles, logins, users and permissions?

SOLUTION:

Who has access to my Databases?

SELECT DP.NAME AS UserName,
       DP.TYPE_DESC AS UserType,
       SP.NAME AS LoginName,
       SP.TYPE_DESC AS LoginType
FROM SYS.DATABASE_PRINCIPALS DP
JOIN SYS.SERVER_PRINCIPALS SP ON DP.PRINCIPAL_ID = SP.PRINCIPAL_ID

Server Roles (view which logins are tied to which server roles)

SELECT P1.NAME AS LoginName,
       P1.TYPE_DESC AS PrincipalType,
       P2.NAME AS RoleName,
       P2.TYPE_DESC AS RoleType
FROM SYS.SERVER_ROLE_MEMBERS ROLES
JOIN SYS.SERVER_PRINCIPALS P1 ON ROLES.MEMBER_PRINCIPAL_ID = P1.PRINCIPAL_ID
JOIN SYS.SERVER_PRINCIPALS P2 ON ROLES.ROLE_PRINCIPAL_ID = P2.PRINCIPAL_ID

Database Roles (determine which users are assigned to database roles)

SELECT P1.NAME AS UserName,
       P1.TYPE_DESC AS UserType,
       P2.NAME AS RoleName,
       P2.TYPE_DESC AS RoleType,
       P2.IS_FIXED_ROLE AS IsFixedRole
FROM SYS.DATABASE_ROLE_MEMBERS ROLES
JOIN SYS.DATABASE_PRINCIPALS P1 ON ROLES.MEMBER_PRINCIPAL_ID = P1.PRINCIPAL_ID
JOIN SYS.DATABASE_PRINCIPALS P2 ON ROLES.ROLE_PRINCIPAL_ID = P2.PRINCIPAL_ID

What can these users do? (indicate which users has specific permissions inside the current database)

SELECT DP.CLASS_DESC AS ClassDesc,
       DP.PERMISSION_NAME AS PermissionName,
       DP.STATE_DESC AS StateDesc,
       OBJECT_NAME(MAJOR_ID) AS ObjectName,
       GRANTEE.NAME AS GranteeName,
       GRANTOR.NAME AS GrantorName
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 -- More detailed query..
WITH P AS
  ( SELECT USER_NAME(P.GRANTEE_PRINCIPAL_ID) AS PRINCIPAL_NAME,
           DP.PRINCIPAL_ID,
           DP.TYPE_DESC AS PRINCIPAL_TYPE_DESC,
           P.CLASS_DESC,
           OBJECT_NAME(P.MAJOR_ID) AS OBJECT_NAME,
           P.PERMISSION_NAME,
           P.STATE_DESC AS PERMISSION_STATE_DESC
   FROM SYS.DATABASE_PERMISSIONS P
   INNER JOIN SYS.DATABASE_PRINCIPALS DP ON P.GRANTEE_PRINCIPAL_ID = DP.PRINCIPAL_ID)
SELECT P.PRINCIPAL_NAME,
       P.PRINCIPAL_TYPE_DESC,
       P.CLASS_DESC,
       P.[OBJECT_NAME],
       P.PERMISSION_NAME,
       P.PERMISSION_STATE_DESC,
       CAST(NULL AS SYSNAME) AS ROLE_NAME
FROM P
WHERE PRINCIPAL_TYPE_DESC <> 'DATABASE_ROLE'
UNION
SELECT R.MEMBER_PRINCIPAL_NAME,
       R.PRINCIPAL_TYPE_DESC,
       P.CLASS_DESC,
       P.OBJECT_NAME,
       P.PERMISSION_NAME,
       P.PERMISSION_STATE_DESC,
       R.ROLE_NAME
FROM P
RIGHT OUTER JOIN
  ( SELECT ROLE_PRINCIPAL_ID,
           DP.TYPE_DESC AS PRINCIPAL_TYPE_DESC,
           MEMBER_PRINCIPAL_ID,
           USER_NAME(MEMBER_PRINCIPAL_ID) AS MEMBER_PRINCIPAL_NAME,
           USER_NAME(ROLE_PRINCIPAL_ID) AS ROLE_NAME
   FROM SYS.DATABASE_ROLE_MEMBERS R
   INNER JOIN SYS.DATABASE_PRINCIPALS DP ON R.MEMBER_PRINCIPAL_ID = DP.PRINCIPAL_ID) R ON R.ROLE_PRINCIPAL_ID = P.PRINCIPAL_ID
ORDER BY 1

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!