T-SQL script to get detailed login permissions

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 <&gt; '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  <&gt; 'R' AND princ.name NOT LIKE '##%'
) P  
where (Role <&gt; 'Public' or ObjectName = db_name())
ORDER BY
P.DatabaseUserName,
P.ObjectName,
P.ColumnName,
P.PermissionType,
P.PermissionState,
P.ObjectType