Role Context

-- List all database roles for a user
SELECT dp1.name AS database_user, dp2.name AS database_role FROM sys.database_role_members drm JOIN sys.database_principals dp1 ON drm.member_principal_id = dp1.principal_id JOIN sys.database_principals dp2 ON drm.role_principal_id = dp2.principal_id WHERE dp1.name = 'user';

-- List all server roles for a user
SELECT sp.name AS server_user, srp.name AS server_role FROM sys.server_role_members srm JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id JOIN sys.server_principals srp ON srm.role_principal_id = srp.principal_id WHERE sp.name = 'USERNAME_HERE';

-- List explicit permissions a user has in the DB
SELECT dp.name AS principal_name, dp.type_desc AS principal_type, perm.permission_name, perm.state_desc, perm.class_desc, obj.name AS object_name FROM sys.database_permissions perm LEFT JOIN sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id WHERE dp.name = 'USERNAME_HERE';

-- If the user is a login mapped from a Windows/domain account
SELECT sp.name AS login_name, sp.type_desc, sp.default_database_name, sl.sysadmin, sl.securityadmin, sl.serveradmin, sl.setupadmin, sl.processadmin, sl.diskadmin, sl.dbcreator, sl.bulkadmin FROM sys.server_principals sp LEFT JOIN sys.syslogins sl ON sp.sid = sl.sid WHERE sp.name = 'DOMAIN\user';