List all access provisioned to a SQL user or Windows user and group directly. List all access provisioned to a SQL user or Windows user/group through a database or application role. List all access provisioned to the public role.

/* ----------------------------------------------------------------------------

SQL Server Security Audit Report

1) List all access provisioned to a SQL user or Windows user/group directly.
2) List all access provisioned to a SQL user or Windows user/group through a
   database or application role.
3) List all access provisioned to the public role.

Columns Returned:

UserType        : Value will be either 'SQL User', 'Windows User', or 'Windows
                  Group'. This reflects the type of user/group defined for the
                  SQL Server account.

DatabaseUserName: Name of the associated user as defined in the database user
                  account.  The database user may not be the same as the server
                  user.

LoginName       : SQL or Windows/Active Directory user account.  This could also
                  be an Active Directory group.

Role            : The role name.  This will be null if the associated
                  permissions to the object are defined at directly on the user
                  account, otherwise this will be the name of the role that the
                  user is a member of.

PermissionType  : Type of permissions the user/role has on an object. Examples
                  could include CONNECT, EXECUTE, SELECT DELETE, INSERT, ALTER,
                  CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may
                  not be populated for all roles.  Some built in roles have
                  implicit permission definitions.

PermissionState : Reflects the state of the permission type, examples could
                  include GRANT, DENY, etc. This value may not be populated for
                  all roles.  Some built in roles have implicit permission
                  definitions.

ObjectType      : Type of object the user/role is assigned permissions on.
                  Examples could include USER_TABLE, SQL_SCALAR_FUNCTION,
                  SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW,
                  etc. This value may not be populated for all roles.  Some
                  built in roles have implicit permission definitions.

Schema          : Name of the schema the object is in.

ObjectName      : Name of the object that the user/role is assigned permissions
                  on. This value may not be populated for all roles.  Some built
                  in roles have implicit permission definitions.

ColumnName      : Name of the column of the object that the user/role is
                  assigned permissions on. This value is only populated if the
                  object is a table, view or a table value function.

Source: http://stackoverflow.com/a/30040784
------------------------------------------------------------------------------ */

--
-- 1.) List all access provisioned to a SQL user or Windows user/group directly.
--

select
    UserType = case princ.type
                   when 'S' then 'SQL User'
                   when 'U' then 'Windows User'
                   when 'G' then 'Windows Group'
               end,
    DatabaseUserName = princ.name,
    LoginName = ulogin.name,
    Role = null,
    PermissionType = perm.permission_name,
    PermissionState = perm.state_desc,
    ObjectType = case perm.class
                     when 1 then obj.type_desc        -- Schema-contained objects
                     else perm.class_desc             -- Higher-level objects
                 end,
    [Schema] = objschem.name,
    ObjectName = case perm.class
                     when 3 then permschem.name       -- Schemas
                     when 4 then imp.name             -- Impersonations
                     else OBJECT_NAME(perm.major_id)  -- General objects
                 end,
    ColumnName = col.name
from
    --Database user
     sys.database_principals as princ
     --Login accounts
left join sys.server_principals as ulogin
    on ulogin.sid = princ.sid
    --Permissions
left join sys.database_permissions as perm
    on perm.grantee_principal_id = princ.principal_id
left join sys.schemas as permschem
    on permschem.schema_id = perm.major_id
left join sys.objects as obj
    on obj.object_id = perm.major_id
left join sys.schemas as objschem
    on objschem.schema_id = obj.schema_id
    --Table columns
left join sys.columns as col
    on col.object_id = perm.major_id
   and col.column_id = perm.minor_id
   --Impersonations
left join sys.database_principals as imp
    on imp.principal_id = perm.major_id
where princ.type in('S', 'U', 'G')
      -- No need for these system accounts
  and princ.name not in('sys', 'INFORMATION_SCHEMA')
union

--
-- 2.) List all access provisioned to a SQL user or Windows user/group through a
--     database or application role.
--

select
    UserType = case membprinc.type
                   when 'S' then 'SQL User'
                   when 'U' then 'Windows User'
                   when 'G' then 'Windows Group'
               end,
    DatabaseUserName = membprinc.name,
    LoginName = ulogin.name,
    Role = roleprinc.name,
    PermissionType = perm.permission_name,
    PermissionState = perm.state_desc,
    ObjectType = case perm.class
                     when 1 then obj.type_desc        -- Schema-contained objects
                     else perm.class_desc             -- Higher-level objects
                 end,
    [Schema] = objschem.name,
    ObjectName = case perm.class
                     when 3 then permschem.name       -- Schemas
                     when 4 then imp.name             -- Impersonations
                     else OBJECT_NAME(perm.major_id)  -- General objects
                 end,
    ColumnName = col.name
from
    --Role/member associations
     sys.database_role_members as members
     --Roles
join sys.database_principals as roleprinc
    on roleprinc.principal_id = members.role_principal_id
    --Role members (database users)
join sys.database_principals as membprinc
    on membprinc.principal_id = members.member_principal_id
    --Login accounts
left join sys.server_principals as ulogin
    on ulogin.sid = membprinc.sid
    --Permissions
left join sys.database_permissions as perm
    on perm.grantee_principal_id = roleprinc.principal_id
left join sys.schemas as permschem
    on permschem.schema_id = perm.major_id
left join sys.objects as obj
    on obj.object_id = perm.major_id
left join sys.schemas as objschem
    on objschem.schema_id = obj.schema_id
    --Table columns
left join sys.columns as col
    on col.object_id = perm.major_id
   and col.column_id = perm.minor_id
   --Impersonations
left join sys.database_principals as imp
    on imp.principal_id = perm.major_id
where membprinc.type in('S', 'U', 'G')
      -- No need for these system accounts
  and membprinc.name not in('sys', 'INFORMATION_SCHEMA')
union

--
-- 3.) List all access provisioned to the public role, which everyone gets by
--     default.
--

select
    UserType = '{All Users}',
    DatabaseUserName = '{All Users}',
    LoginName = '{All Users}',
    Role = roleprinc.name,
    PermissionType = perm.permission_name,
    PermissionState = perm.state_desc,
    ObjectType = case perm.class
                     when 1 then obj.type_desc        -- Schema-contained objects
                     else perm.class_desc             -- Higher-level objects
                 end,
    [Schema] = objschem.name,
    ObjectName = case perm.class
                     when 3 then permschem.name       -- Schemas
                     when 4 then imp.name             -- Impersonations
                     else OBJECT_NAME(perm.major_id)  -- General objects
                 end,
    ColumnName = col.name
from
      --Roles
     sys.database_principals as roleprinc
     --Role permissions
left join sys.database_permissions as perm
    on perm.grantee_principal_id = roleprinc.principal_id
left join sys.schemas as permschem
    on permschem.schema_id = perm.major_id
    --All objects
join sys.objects as obj
    on obj.object_id = perm.major_id
left join sys.schemas as objschem
    on objschem.schema_id = obj.schema_id
    --Table columns
left join sys.columns as col
    on col.object_id = perm.major_id
   and col.column_id = perm.minor_id
   --Impersonations
left join sys.database_principals as imp
    on imp.principal_id = perm.major_id
where roleprinc.type = 'R'
  and roleprinc.name = 'public'
  and obj.is_ms_shipped = 0
order by
    UserType,
    DatabaseUserName,
    LoginName,
    Role,
    [Schema],
    ObjectName,
    ColumnName,
    PermissionType,
    PermissionState,
    ObjectType;