Your Biggest SQL Server Security Vulnerability Is Your sysadmin Role Members [30 SQL Server Security Checks in 30 Days]

It was previously noted that having the sa login enabled is a common vulnerability, because that login is in the all-powerful sysadmin role by default. So, here’s my question for you: when was the last time you reviewed who was in your SQL Server instance’s sysadmin role?

Since our security checks often find Invalid windows logins in the sysadmin role, I get the feeling folks don’t review this role very often. This is a fixed server role in your instance who’s members “can perform any activity in the server.”  And even that seems like an understatement.

It’s crucial that you only have necessary groups and logins in this role, because they can do any of the following:

• Create another login in the sysadmin role.
• Remove permissions for another member of the sysadmin role.
• Drop logins, databases, jobs, and anything else.
• Stop SQL Server services.
• Enable xp_cmdshell, which potentially can be used to download and install malware.
• Enable other settings that can be used to easily escalate privileges.
• Disable audits or delete logs that indicate nefarious activity.

I can keep going, but I think you get the idea. If you are a sysadmin, here’s how you can see which enabled server principals are enabled assigned to the sysadmin role (excluding the sa login).

SELECT l.name
FROM master.sys.syslogins l
WHERE l.sysadmin = 1
    AND l.name <> SUSER_SNAME(0x01)
    AND l.denylogin = 0
    AND l.name NOT LIKE 'NT SERVICE\%'
    AND l.name <> 'l_certSignSmDetach';

This script ignores the sa login (or whatever some crazy folks have renamed it to) as well as any logins that are denied access. It also ignores a few built-in logins that you didn’t add to the role and don’t want to remove.

If you find there are Windows groups included, don’t worry, you can see which logins are in those groups with a handy extended stored procedure that’s included in your instance: xp_logininfo. To see the members of any group, just execute this script and replace the THEDOMAIN and TheGroup with, well, the domain and the group you want to review.

EXEC xp_logininfo 'THEDOMAIN\TheGroup', 'members'

Remember, this group can do anything inside of SQL Server, and potentially more outside of SQL Server. Even if these are all stand-up folks, everyone in this group is one phishing email click away from compromising your instance and possibly your entire environment. Whether or not you are required to do so, make it a priority to periodically review the member of the sysadmin role.