SQL Server Blog

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

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.

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.

Avatar
Article by Jeff Iannucci
Jeff loves to help others save time. Whether improving query performance or reducing downtime for maintenance tasks or migrations, he’s not only passionate about discovering ways to save our most valuable resource, but also about sharing what he’s learned. Starting as an accidental DBA in 1998, Jeff has worked extensively with SQL Server database design, administration, and T-SQL development. He appreciates any opportunity to share his knowledge with the community, and has been presenting at user groups and writing blog posts since 2018. He was chosen as an IDERA ACE for 2020, and in 2021 became an author for Pluralsight. When not resolving database issues, he enjoys watching Indycar racing, attending Arizona Diamondbacks and Phoenix Suns games, and cooking Italian food for his wife and four children.

Subscribe for Updates

Name

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This