Who's Got What

Every once in a while it helps to stand back and review the security privileges for everyone, down at the database level.  I've found this query to be exceedingly helpful in ferreting out the roles that have been assigned:

WITH RT AS
(SELECT name AS RoleType, principal_id
FROM sys.database_principals
WHERE (type_desc = 'DATABASE_ROLE'))
SELECT P.name, RT.RoleType FROM RT
INNER JOIN sys.database_role_members AS RM
ON RT.principal_id = RM.role_principal_id INNER JOIN
sys.database_principals AS P
ON P.principal_id = RM.member_principal_id
ORDER BY P.name, RT.RoleType

You need to run this either with a USE statement, or connected to a specific database (you should run it for each database you are reviewing). Sometimes it's necessary to perform a deep dive on a specific individual, in which case you'll want to run this little puppy:

execute as user = 'OURDOMAIN\chkusername'
select * from fn_my_permissions(null, 'DATABASE') -- Leave as the literal
order by subentity_name, permission_name
revert