Every so often I'll see a question in the forums about restricting system
administrator access to
SQL Server. The topic
specifically revolves around the BUILTIN\Administrators login, a Windows group
that corresponds to the local Administrators group for that server. By
default, the BUILTIN\Administrators login is added to SQL Server during the
install and made a member of the sysadmin fixed
server role. That means the administrators on the system have the ability to do
whatever they want to within SQL Server.
Needless to say, this gives some DBAs fits (myself included), especially if
there is sensitive data, such as payroll information or customer credit card
numbers, within the SQL Server in question. The DBA would like nothing more than
to ensure only a few personnel have the ability to access the system in
question and even fewer still with complete control over SQL Server. However,
there are a few critical "gotchas" with removing the BUILTIN\Administrators
group, especially if you have SQL Server installed in a clustered environment or
are using Full Text Search capabilities. Since most of the technical
difficulties can be overcome with prior planning, much of the discussion
focuses on the pros and cons of removing access from the administrators, so I'll
The Rationale for Removing BUILTIN\Administrators
In security there is the principle of least privilege, one I'll speak on a lot
in these columns. This principle is simple: give people the rights to do their
jobs and nothing more. If I'm talking about a payroll system, the average system
administrator has no reason to be able to query the SQL Server and find out the
salary of her co-workers. Nor does the system administrator have any reason to
download the credit card numbers from the customer database on one of his
servers. System administrators with such rights violate the principle of least
The Rationale for Keeping BUILTIN\Administrators
First there's the concept of trust. System administrators are entrusted with
a lot of power. After all, what's to stop an administrator with control over the
mail server from sending a message to all employees as the CEO? What's to stop
the system administrator from deleting a bunch of critical research documents?
What's to stop an administrator from installing a keystroke logger on the head
of HR's workstation? The answer to all of those questions is one word:
trust. Just as the DBAs are entrusted with the data, so the system
administrators are entrusted with the servers and the infrastructure. These are
positions where you must hire trustworthy individuals.
Let's not stop there. In the real world there is the question of "What
good does it do?" and it's a very valid question when it comes to SQL
Server and the BUILTIN\Administrators login. After all, there is nothing
stopping an administrator from resetting the password on a database
administrator's account and then logging on as the DBA. Also, there's nothing to
stop an administrator from simply stopping the SQL Server service and copying
off the database files (though EFS may slow the admin down a bit). Similarly,
there's nothing stopping the admin from grabbing a backup tape and, you guessed
it, grabbing the data from it. Finally, if the DBAs are all contained in a group
and that's how the DBAs gain access to their SQL Servers, there's nothing to stop an administrator from adding himself or herself
to the group just long enough to get the data and them remove the group
membership when the dirty deed is done. There's a lot the savvy system
administrator can do to gain access.
Audit, Audit, Audit and Check those Logs!
Tom Clancy wrote a bestseller in the late 80's entitled Cardinal of the
Kremlin which presented the scenario of a US spy buried deep within the
Kremlin itself. Recent events within the US's Federal Bureau of Investigation has shown that foreign spies had
penetrated to the highest levels of the organization. What does this prove? It
proves that sometimes people you think you can trust turn out to be ones you
can't. We could quickly devolve into saying, "Trust no one," and put
up draconian measures to ensure our systems are secure. However, this will
severely affect productivity and users will scream (and rightfully so). This puts every organization in a difficult situation: how does
it allow its system administrators to do their jobs without putting the company unnecessarily
The key to bridge the gap is to audit well and have someone review those
audit logs. In SQL Server this is a little harder to do since that means parsing
log files and setting up traces or running Profiler interactively (keep in mind
you're not just looking for logon/logoff). While difficult, it can be done (and
I'll cover how in a later article). However, you can keep all the audit logs in
the world but they do little good if no one is reviewing them. And here's where
a lot of shops run into a big problem: who has the time? When it comes to
security someone is going to have to "make the time." Remember, it's
not just a savvy system administrator who may make off with the data. As hard as
it is to think, it could be another DBA. Or it could just be a weakness in the
system that's tripped upon accidentally. Therefore, proper auditing and proper
review of that audit data is part of any successful security policy. Keep in
mind that this auditing isn't just on the SQL Server itself. After all, a SQL
Server trace isn't going to catch an administrator resetting a DBA's password
and using it. Auditing must be a pervasive part of all areas of your
The Meat of the Matter: The Technical Details
You've carefully weighed all the pros and cons with removing BUILTIN\Administrators
and you've finally decided that in keeping with the principle of least
privilege, they have to go. What are the technical details? What can I do to try
and recover if I make a mistake? What are the issues I need to be aware of when
removing this group? First let's talk about recovery.
The DBA's backdoor: The SA account
The sa account is a known account. It exists on every SQL Server 2000
installation and it is always a sysadmin over the entire SQL Server. Therefore,
if you know the password, you can always use the sa account (with one potential
key exception: clusters). Yes, even if your SQL Server is set to use Windows
(NT) Authentication, this is your magical key into the system. Changing a SQL
Server to mixed mode is as simple as altering a registry key and then stopping
and restarting the respective SQL Server service. So the very first thing to do
is make sure you know the sa account password. If you don't know it (such as you
only use Windows authentication), set it. The easiest way is with sp_password
and a sysadmin level account, like so:
member of the sysadmin role doesn't need the old password
EXEC sp_password @loginame = 'sa', @new = '<your password here>'
If you remove BUILTIN\Administrators and suddenly realize you can't get into
the system, the sa account is what you can use to add that account back in
immediately. If your SQL Server isn't in Mixed Mode (best practice says not to
unless you have no other choice), then you're going to have to edit the
registry. You'll be looking for the following value:
* HKLM is short for HKEY_LOCAL_MACHINE
To change the mode, change the data. You should have one of two
Changing to Mixed Mode is as easy as changing the value to 2 and stopping and
restarting the SQL Server instance.
Note: If changing the authentication mode is this easy for you,
the DBA, it'll be this easy for anyone else, too. This is why, even if you've
set your SQL Server to Windows Authentication, that you should set a strong
password for the sa account!
Copy the Master Database and Prepare Your Backups
Security settings such as who has logon rights and who is a member of what
server role is contained in the master database, specifically in the sysxlogins
table. Before making any changes, stop the SQL Server service and copy all files
(.mdf, .ndf, .ldf) related to the master database to a protected location.
Remember the original locations for the files. If something goes wrong, a
possible roll-back is to simply replace the master database files. Alternately
(and in addition, I'd say), take a backup of the master, model, and msdb
databases and set them aside. If all else fails, you could potentially run
rebuildm.exe, which will put in place generic system databases, and then restore
over the top of them.
Dealing with Clustered Instances: Proceed with Great Caution
Clustered instances of SQL Server are very delicate creatures. If you
mistakenly remove the BUILTIN\Administrators account without taking the proper
prior steps, you may be left with a SQL Server instance that refuses to come up.
The "backdoor" I gave with the sa account won't matter because in
order to log on to SQL Server, it must be running. Therefore, if you are
going to remove the BUILTIN\Administrators group from a SQL Server cluster, do
so with great caution. I cannot stress this enough. The last thing you want to
have to do is rebuild a SQL Server clustered instance from scratch.
The first thing you'll need to do is find out what account the cluster
service is running under. This account periodically checks to see if the SQL
Server instance is alive. Though this doesn't seem like a whole lot, Microsoft
says this account is going to need special privileges (sysadmin). The easiest
way to check what account the cluster service is running under is to bring up
Computer Management (Start | Run | CompMgmt.msc or right-click on My Computer
and choose Manage), manage one of the nodes of the cluster, and then expand
Services and Applications | Services and see what account the Cluster Service is
configured to Log On As. Just to be safe (or if you don't have the rights),
verify with your system administrator who handles the cluster as well. The
account that runs the cluster is the one you want to give rights to.
The account in question needs a login to SQL Server and it needs sysadmin
rights, as per Microsoft's documentation. Two short commands are required to
make this happen if you're doing it via T-SQL. Otherwise, create the login
normally using Enterprise Manager and grant it the appropriate server role.
Here's the T-SQL:
the cluster account the ability to log on
EXEC sp_grantlogin [<Domain>\<Account>]
' Make it a member of the sysadmin role
EXEC sp_addsrvrolemember [<Domain>\<Account>], 'sysadmin'
Of course, if your system administrator knows the cluster account password,
he or she can always use that account to come in to SQL Server with full rights.
The Microsoft Knowledge Base article I cite in Additional Resources uses the
word "impede" to describe the process of removing BUILTIN\Administrators
for this reason. Of course, only a chosen few should know said password and that
should assist greatly in narrowing down whodunit if ever an issue arises.
Now, if you have Full Text installed as a clustered resource (even if you
aren't using it), you have one more step before removing the BUILTIN\Administrators
account. You'll need to follow the steps in the next section.
Full Text Search: It Needs LocalSystem
The Full Text Search service is also known as Microsoft Search if you're
looking for it under your list of services. Microsoft has made it quite clear
that the only way the Full Text Search service is supported is if it is running
under LocalSystem. Whether or not you're running it on a clustered instance of
SQL Server makes no difference. The Knowledge Base article I've cited as an
additional resource only talks about a bug where the Full Text Search service
would cause the CPU to go to 100% if it wasn't configured to run under
LocalSystem. Microsoft states that only LocalSystem is supported. But they've
fixed the bug so no big deal, right?
Not exactly. While LocalSystem is a pretty heavy set of privileges to give to
a service which is just supposed to do Full Text Search, Microsoft hasn't left
any choice here. Part of the problem is that Microsoft hasn't said why only
LocalSystem is supported and it's not likely that they will do so any time soon.
If you want support you have to leave the service alone and let it run under
LocalSystem. If you change the service account, there's really no information
available as to the consequences.
With that said, the LocalSystem account may be a little tricky to add if
you've never seen how to properly refer to that account. It's not hard, but it
is a bit unusual. Also, the LocalSystem account will need syadmin rights. Here's
the T-SQL to do it:
LocalSystem is known as [NT Authority\System] for SQL Server
' and it needs logon rights
EXEC sp_grantlogin [NT Authority\System]
' Make it a member of the sysadmin role
EXEC sp_addsrvrolemember [NT Authority\System] , 'sysadmin'
The SQL Server and SQL Server Agent Service Accounts
Last but certainly not least, take care of the SQL Server and SQL Server
Agent service accounts. Explicitly grant these accounts the right to logon to
SQL Server and add them to the sysadmin role. I do this as a general practice
even if I don't remove BUILTIN\Administrators. The last thing I want is for a
SQL Server to come down because someone modified a group and suddenly my service
account loses the access it needs. This can be a tremendous problem to
If we follow the principle of least privilege, the BUILTIN\Administrators
login has to go. However, before I advise anyone to do this, I also advise them
to be fully aware of the predecessor steps and the work-arounds available to the
system administrators. There's nothing like breaking a SQL Server or promising
management one is secure only for a system administrator to raid it.
If you go forward with removing BUILTIN\Administrators, prepare to do a
little research first. Understanding the configuration is an absolute must. Know
the service accounts. Know what pieces and parts you have installed. And review
the documentation available. I've cited three relevant Knowledge Base articles
from Microsoft that discuss the topics I've covered. Also check in Microsoft's
Knowledge Base before proceeding in case they have a new article out covering a
discovered bug or the like. Then test the change in a non-production
environment, if at all possible. Only once you are comfortable with the steps
and understand the consequences in your environment, proceed forward.
As always, if you have questions, feel free to ask them in the forums. There
are quite a few DBAs who regularly participate who have tackled removing BUILTIN\Administrators
and they've done so in just about every environment imaginable. Also, if you
have comments or suggestions on this article or if you've spotted an error,
please use the Your Opinion tab.
MSDE Security and Authentication- covers the Registry keys for SQL
Server (it applies to the full SQL Server engine as well)
How to Impede Windows NT Administrators from Administering a Clustered SQL
Server- The How-To for Clusters
Microsoft Search Service May Cause 100% CPU Usage if BUILTIN\Administrators
Login is Removed- What is needed if you run Full-Text Search (this
includes for clusters)
Use BUILTIN\Group to Grant Access to Predefined Windows NT Groups-
While not exactly germane to the discussion of this article, if you have
need to grant rights to some of the predefined Windows groups, this article
discusses how to do so.
Brian Kelley. http://www.truthsolutions.com/
Author of Start
to Finish Guide to SQL Server Performance Monitoring.