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.
If you have been reading along with our series of “30 SQL Server Security Checks in 30 days” posts, you’ve probably noticed a theme for a lot of these posts, where we recommend reviewing which principals have CONTROL SERVER permissions or are in the sysadmin role.
Full disclosure: I hope you aren’t tired of that yet, because there will be more of those posts.
However, today I wanted to turn your attention towards what might be considered potential shadow members of the sysadmin role. These are the members of the server’s local Windows Administrators group.
Way back in versions of SQL Server that are no longer supported, it was common practice on installation of a new instance to not only grant members of this group permissions in SQL Server, but also to add them to the sysadmin role. They would show up in your SQL Server logins as a “BUILTIN\Administrators” group.
This makes a bit of sense, with the thinking that if someone is trustworthy enough at the operating system level, they should probably be trustworthy enough at the database level as well.
Yeah, probably.
However, systems administrators and database administrators are often different people with different responsibilities, and the sysadmin role is so powerful that we don’t want to automatically be adding users to this role if database administration isn’t part of their work requirements. We want to adhere to the principle of least privilege when it comes to security, so we don’t want permissions assigned unless they are necessary.
Why? Because every account that gets added is one more possible attack vector. Every additional member of the sysadmin role is potentially one phishing email away from being compromised, and we don’t want to increase the odds of that by adding more logins or groups to the most powerful role by default.
So why is this even a post? I’m sorry, I know I’m asking a lot of questions, but I am getting to the point.
If you aren’t an avid reader of this blog, you might not be aware that our own Mike Lynn wrote a post last year about adding SQL Server access when you’re locked out. If you haven’t read it, you should, because it’s not only helpful if you are a database administrator who somehow lost access to a SQL Server instance, but it’s also educational in understanding the relationship between the local Administrators group and the SQL Server sysadmin role – even with default permissions no longer being granted.
Long story short, local Administrators can add their Windows accounts to the sysadmin role with just a few commands and a restart of the SQL Server service.
Because of this, make it a priority to review the members of the local Administrators group for every Windows server that has a SQL Server instance. Just as you may find accounts in the sysadmin role that no longer require those elevated permissions, you may find similar accounts in the Administrators group that can be removed. Every account you can remove from these groups is one less potential way to attack your data.
