When you audit who has sysadmin rights on a SQL Server instance, the answer is often an Active Directory group rather than a list of people. A login such as DOMAIN\SQL DBA tells you a group has full control, but not who is actually in it. Many DBAs do not have access to Active Directory tools, so how do you find out?
The answer: xp_logininfo
SQL Server ships with an extended stored procedure, xp_logininfo, that asks Active Directory on your behalf using the SQL Server service account. You do not need AD permissions yourself.
EXEC xp_logininfo 'DOMAIN\SQL DBA', 'members';
This returns one row for each member of the group, including the account name, its type (user or group), and its privilege level.
Step 1: Find who holds sysadmin
First, list every login in the sysadmin role, so you can see which entries are Windows groups:
SELECT p.name AS login_name,
p.type_desc AS login_type,
p.is_disabled
FROM sys.server_role_members rm
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.server_principals p ON rm.member_principal_id = p.principal_id
WHERE r.name = 'sysadmin'
ORDER BY p.type_desc, p.name;
Any row with a login_type of WINDOWS_GROUP needs expanding.
Step 2: Expand each group
Run xp_logininfo with the 'members' option for each group found above. Note that it only goes one level deep: if a group contains another group, run it again against the nested group.
Step 3: Check an individual’s access path
You can also go the other way and ask how a particular user gets in:
EXEC xp_logininfo 'DOMAIN\jsmith', 'all';
The permission path column shows which group grants that user their access.
Things to watch for
- Error 15404 usually means the SQL Server service account cannot query AD, or the group name is wrong or no longer exists. A deleted group that still has a login is worth flagging in itself.
- BUILTIN\Administrators, if present, means every local administrator on the server is a sysadmin.
- NT SERVICE accounts in sysadmin are normally expected and can be left alone.
Why it belongs in your audit
Group membership changes outside SQL Server and without the DBA knowing. Expanding sysadmin groups regularly, and keeping the output, gives you and your IT manager a clear record of exactly who has full control of the estate.
Clive Roberts DBA