This script is very helpful when you need to know all the permissions a user have in detail.
SELECT distinct
@@SERVERNAME as ServerName
, UserName as LoginName
, UserType as LoginType
, DatabaseUserName
, Role
, PermissionType
, PermissionState
, DatabaseName = db_name()
, ObjectName -- can be an object or the entire database
, ObjectType
, ColumnName
-- , item -- used to check consistencies in each subquery
FROM (
SELECT
UserName = princ.name,
UserType = CASE princ.type
WHEN 'S' THEN 'SQL'
WHEN 'U' THEN 'Windows'
WHEN 'G' THEN 'Windows Group'
WHEN 'R' THEN 'Database Role'
ELSE princ.type
END,
DatabaseUserName = princ.name,
Role = princ.name,
PermissionType = perm.permission_name,
PermissionState = perm.state_desc,
ObjectType = obj.type_desc,
ObjectName = coalesce(OBJECT_NAME(perm.major_id), db_name()),
ColumnName = col.name,
1 as item
FROM
sys.database_principals princ
LEFT JOIN sys.login_token ulogin on princ.sid = ulogin.sid
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id
LEFT JOIN sys.columns col ON col.object_id = perm.major_id AND col.column_id = perm.minor_id
LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
WHERE perm.permission_name <> 'CONNECT'
UNION
SELECT
UserName = memberprinc.name ,
UserType = CASE memberprinc.type
WHEN 'S' THEN 'SQL'
WHEN 'U' THEN 'Windows'
WHEN 'G' THEN 'Windows Group'
WHEN 'R' THEN 'Database Role'
else memberprinc.type
END,
DatabaseUserName = memberprinc.name,
Role = roleprinc.name,
PermissionType = perm.permission_name,
PermissionState = perm.state_desc,
ObjectType = obj.type_desc,
ObjectName = db_name(),
ColumnName = col.name,
2 as item
FROM
sys.database_role_members members
INNER JOIN sys.database_principals roleprinc ON roleprinc.principal_id = members.role_principal_id
INNER JOIN sys.database_principals memberprinc ON memberprinc.principal_id = members.member_principal_id
LEFT JOIN sys.login_token ulogin on memberprinc.sid = ulogin.sid
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = roleprinc.principal_id
LEFT JOIN sys.columns col on col.object_id = perm.major_id AND col.column_id = perm.minor_id
LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
UNION
SELECT
UserName = roleprinc.name,
UserType =
CASE roleprinc.type
WHEN 'S' THEN 'SQL'
WHEN 'U' THEN 'Windows'
WHEN 'G' THEN 'Windows Group'
WHEN 'R' THEN 'Database Role'
ELSE roleprinc.type
END,
DatabaseUserName = roleprinc.name,
Role = roleprinc.name,
PermissionType = perm.permission_name,
PermissionState = perm.state_desc,
ObjectType = obj.type_desc,
ObjectName = coalesce(OBJECT_NAME(perm.major_id), db_name()),
ColumnName = col.name,
3 as item
FROM
sys.database_principals roleprinc
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = roleprinc.principal_id
LEFT JOIN sys.columns col on col.object_id = perm.major_id AND col.column_id = perm.minor_id
LEFT JOIN sys.objects obj ON obj.object_id = perm.major_id
UNION
SELECT
UserName = princ.name collate Latin1_General_CI_AS,
UserType = CASE princ.type
WHEN 'S' THEN 'SQL'
WHEN 'U' THEN 'Windows'
WHEN 'G' THEN 'Windows Group'
WHEN 'R' THEN 'Database Role'
ELSE princ.type
END ,
DatabaseUserName = princ.name collate Latin1_General_CI_AS,
Role = CASE
WHEN logins.sysadmin = 1 THEN 'sysadmin'
WHEN logins.securityadmin = 1 THEN 'securityadmin'
WHEN logins.serveradmin = 1 THEN 'serveradmin'
WHEN logins.setupadmin = 1 THEN 'setupadmin'
WHEN logins.processadmin = 1 THEN 'processadmin'
WHEN logins.diskadmin = 1 THEN 'diskadmin'
WHEN logins.dbcreator = 1 THEN 'dbcreator'
WHEN logins.bulkadmin = 1 THEN 'bulkadmin'
ELSE 'Public'
END,
PermissionType = perm.permission_name,
PermissionState = 'GRANT',
ObjectType = NULL,
ObjectName = princ.default_database_name,
ColumnName = NULL,
4 as item
FROM sys.server_principals princ
INNER JOIN sys.syslogins logins ON princ.sid = logins.sid
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id
WHERE princ.type <> 'R' AND princ.name NOT LIKE '##%'
) P
where (Role <> 'Public' or ObjectName = db_name())
ORDER BY
P.DatabaseUserName,
P.ObjectName,
P.ColumnName,
P.PermissionType,
P.PermissionState,
P.ObjectType