By: K. Brian Kelley | Updated: 2014-10-23 | Comments | Related: > Auditing and Compliance
Problem
I'm responsible for the security of my organization's SQL Servers. Lately I've been looking outside of SQL Server and wondering what I should be checking. Someone mentioned the Administrators group and that made me think about other groups on the server. What groups should I be auditing and why?
Solution
Outside of SQL Server, at the local server, there are several built-in groups you should audit the membership of:
- Administrators
- Power Users
- Remote Desktop Users
Administrators
There are several sayings, but they all basically mean this, "If I am an administrator of your server, it's not your server anymore." As an administrator I can do just about anything, to include bypassing controls that might normally keep me out of a SQL Server instance. As a result, knowing who is a member of this security group is critical. Audit it frequently.
Power Users
I have found as I've talked to auditors and DBAs that quite a few aren't familiar with Power Users. If you're dealing with a newer server operating system, Windows Server 2008 or above, then the Power Users group doesn't have any more rights than the normal Users group. However, if you're still running Windows Server 2003 servers, do note that on that OS the members of the Power Users group can control services. This means they can stop services, set them to disabled, change the login for a service, and other such things which can cause a denial of service for a SQL Server. Therefore, if you have an older OS, audit Power Users.
On the newer OSes, if you are dealing with an application installed on your SQL Server that is not certified as part of the Windows Logo program, investigate whether or not that application uses the Power Users group. If it does, then an administrator may have changed the security on the server to allow Power Users to have the same rights it did on legacy OSes. If that's the case, you'll need to audit this group even if you're on Windows Server 2008 and above.
Remote Desktop Users
Normally users cannot log on to a server through Remote Desktop, only administrators can. However, if a user is a member of Remote Desktop Users, then it is possible for the user to connect if RDP connectivity/functionality is enabled. When you look at Microsoft security patch ratings, the ones that require a login in order to exploit tend to be rated less than Critical. Some organizations roll Critical patches ahead of Important ones. If there are users in Remote Desktop Users, then those users might be able to take advantage of this dichotomy for patch deployment. Also, consider that there's always the possibility of stray information lying in a file somewhere a logged on user can access, information that shouldn't be exposed. Obviously, if Remote Desktop Users is kept member less, that reduces this concern. If it's not, then you have to consider that type of issue. Because of these risks, I always recommend auditing Remote Desktop Users.
SQL Server Specific Groups
For most SQL Server configurations, there are local groups corresponding to the SQL Server install. For instance:
This is a test system which has every version from SQL Server 2005 to 2012 installed. You likely won't see as many groups on one of your servers.
If you look at the descriptions for the groups, you'll see that each one gives specific permissions and/or access. As a result, they should be audited regularly.
What Else?
Any created groups are immediately suspect in my book. Know why they are there, what access they have, and who the members are. Some of the groups many not be of concern, the problem is you won't know until you investigate.
Next Steps
- Learn how to audit the Administrators group using PowerShell.
- Read how a local administrator can gain access to a SQL Server instance.
- Check where you write your backups to ensure that folder is properly protected.
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-10-23
