sys.database_permissions

This is just a very quick script to review your permissions at the database level, for each principal. Remember, a 'principal' is an entity that can utilize SQL Server resources, like any one of the following:

  SQL user
  Windows user
  Windows group
  Application role
  Database role
  User mapped to a certificate
  User mapped to an asymmetric key

This logic is just a quick way to return privileges defined for each of the above principals:

     WITH permsCTE as

      (

     SELECT

        USER_NAME(p.grantee_principal_id) 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   )

     --users

    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) [role_name]

    FROM 

      permsCTE p

    WHERE 

      principal_type_desc <> 'DATABASE_ROLE'

         UNION

     --role members

    SELECT

      rm.member_principal_name,rm.principal_type_desc,p.class_desc, 

      p.object_name,p.permission_name,p.permission_state_desc,

      rm.role_name

    FROM permsCTE p RIGHT OUTER JOIN (

       SELECT 

         role_principal_id, 

         dp.type_desc [principal_type_desc], 

         member_principal_id,

         user_name(member_principal_id)  

         [member_principal_name],

         user_name(role_principal_id) 

         [role_name]

        FROM

            sys.database_role_members rm INNER JOIN sys.database_principals dp

              ON rm.member_principal_id = dp.principal_id ) rm

      ON rm.role_principal_id = p.principal_id

     ORDER BY 1

Here's a screenshot of the output from one of my instances:

See here for more information on sys.database_permissions and sys.database_principals:

  https://msdn.microsoft.com/en-us/library/ms188367.aspx
  https://msdn.microsoft.com/en-us/library/ms187328.aspx