Security Checkup

Sure you maintain the security on your instances, setting up the logins and the database mappings. You probably also assign folks to a couple of database roles you maintain to restrict access to certain critical tables. Still though, employees come and go, and even though your company likely follows a termination procedure that loops you in for disabling logins, security tasks still fall through the cracks.

Help is on the way. Run this:

EXEC xp_logininfo

Do you see any ex-employees listed with admin rights?  Oops. Next check the members of the groups that are listed:

EXEC xp_logininfo @acctname =  '[group_name]', @option = 'members'

Maybe you still have a little more cleanup work to perform, eh?

Here's another way to see the active sysadmins:

SELECT l.loginname
FROM SYS.syslogins l
join sys.server_principals ss on l.[name] = ss.[name]
where ss.is_disabled = 0
and l.sysadmin = 1 

Finally, within a single database, this query is useful for reviewing folks' rights:

;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 RT.RoleType, P.name