sqlserver_permisions_report.sql

-- roles
SELECT 
  us.name as userName, 
  us.authentication_type_desc,
  'Role' as [PermissionType],
  CAST(rol.name COLLATE Latin1_General_CI_AS_KS_WS as nvarchar(128)) as [Permission/Role], 
  CAST('' COLLATE Latin1_General_CI_AS_KS_WS as nvarchar(60)) as [class], 
  CAST('' COLLATE Latin1_General_CI_AS_KS_WS as nvarchar(60)) as [type], 
  CAST(null as sysname) as [ObjectName] 
FROM 
  sys.database_principals us
  INNER JOIN sys.database_role_members rom 
    ON rom.member_principal_id = us.principal_id
  INNER JOIN sys.database_principals rol 
    ON rom.role_principal_id = rol.principal_id
UNION
-- securables
SELECT 
  us.name as userName, 
  us.authentication_type_desc,
  'Securable' as [PermissionType],
  per.permission_name, 
  per.class_desc, 
  obj.type_desc, 
   ISNULL(obj.name, tt.name)
FROM 
  sys.database_principals us
  INNER JOIN sys.database_permissions per 
    ON us.principal_id = per.grantee_principal_id
  LEFT JOIN sys.objects obj 
    ON per.major_id = obj.object_id
  LEFT JOIN sys.table_types tt 
    ON per.major_id = tt.user_type_id
ORDER BY 1,2