Removing builtin\administrators: Some pitfalls to avoid
Shortly after accepting the position as a new DBA for a large law firm,
I began looking for ways to tighten security and protect the firm’s
databases. I first learned about the builtin\administrators account
from Brian Knight’s article, 10 Steps to Securing Your Server.
Please note that all screen shots in this article are from my home computer and are not from servers owned by my employer.
As you probably know, the builtin\administrators group account is granted the
sysadmin role when SQL Server is installed. This account allows anyone
who is an administrator on the server to have full sysadmin rights. Well,
that is helpful if you forget the sa password and need
another way in, but maybe the server administrators
don’t really need access to SQL Server. OK, you guys who have to deal
with Sarbanes-Oxley can move on to another article: I’m sure you have
already addressed this issue. But, hopefully, what I have learned will
help the rest of you.
Do your homework
I didn’t get very far in researching the removal of this group when
I began to hear horror stories from colleagues who had removed
the account with some adverse consequences. Of course I searched
Books On Line, Microsoft’s website, and SQL Server Central for
related articles to learn what could possibly go wrong. I don’t really want
to cover the special cases, like clustered servers.
See Brian Kelley’s article, SQL Server Security: Security Admins for
more information. I would like to share the unexpected events I encountered
when removing the account from a handful of SQL Servers. Two things
I did learn from the articles: Make sure you have configured another account
with sa rights or know the sa password (test this first!!!) and that SQL Agent
must not be configured to start up with the local system account.
The first time I removed builtin\administrators from an instance of SQL
was actually by accident. I had a job set up to copy the backup file each
night from production server A (not its real name) and restore to the
testing/training server (B). One step in the job removed accounts that
were not needed on server B. The way I accomplished this was by adding the
accounts and required access start and end dates to a table. My script grants or removes
logins based on the accounts listed and dates. That way, when I need to
give access to someone temporarily, which happens frequently, all I have
to do is add the account and dates to the table and forget about it. A month
or so after implementing this job, I decided to remove
builtin\administrators from B and, amazingly, it was already gone!
My script had removed the account without my knowledge and luckily,
nothing bad had happened. Since I keep track of sa passwords in a password safe
and the domain DBA group has sysadmin rights on all the SQL Servers, a mess had been avoided.
Expect the unexpected
My next experience involved a development server (C). The IS department
hired a new network admin guy who also did some scripting. He needed a
database for some work he was doing, so I set up a database on C and made
him dbo. Since he was also a domain admin, and I didn’t want him to have
full rights, I removed builtin\administrators from server C. I had already
added our DBA domain group to the sysadmin role and knew the sa password.
Both SQL and SQL Agent were configured to start up with a domain account, so I was
certain that everything would be fine.
Of course I was wrong. SQL Agent wouldn’t start -- no error message,
nothing in Event Viewer – it just wouldn’t start. I tried from Enterprise
Manager, the Service Manager and the services applet, I tried a reboot.
Nothing worked. OK, Mulder, there has to be a logical explanation here.
I clicked around in EM until I found it. The startup account had an odd
property in the server access column: “Via group membership”. I remember
that showed up when I changed the start up account on C a few months ago.
Figure 1: Via group membership
I brought up the properties of the account and changed the “Security access” setting to “Grant access”. That worked! SQL Server Agent was back in business.
Figure 2: Security access setting
My next experience involved the production server, A, that I mentioned
earlier. Since the test server, B, had been running fine without
builtin\administrators, I decided it was time to remove the account from
server A. I made sure that my domain DBA group had sysadmin rights, knew the sa
password, and that the startup account looked OK. What could go wrong?
The next morning, I discovered that five jobs running DTS packages
had failed on another server, D. It took a few minutes for me to
realize what had happened. The connections in the DTS packages pointing to server A used Windows authentication. So, the account used to run SQL Agent on D was evidently a domain admin (a problem for another day) and had now lost its rights to SQL on server A. To fix the problem, I just added server D’s startup account to A and gave explicit select rights to the tables it needed. Problem solved.
The next step was to remove builtin\administrators from server D.
Incredibly, nothing went wrong.
Finally, after this much experience, I was ready for the two HR servers:
development E and production F. This set of servers used the local system
account to start SQL and a domain account to start SQL Agent so that job
notifications could be sent via e-mail. After removing
builtin\administrators on E, SQL started up as did the Agent,
but the backup jobs all failed. Changing the SQL startup account to
the same account as the Agent solved the problem. After a week to make
sure no other problems showed up, I modified the startup account and
successfully removed builtin\administrators from F.
Maybe you will not encounter the same problems I did when removing the
builtin\administrators account from your server, but if you do, I hope this
article will give you the solution that you need. One point that I must
stress: Don’t lock yourself out of SQL. Make sure you have two accounts
with sysadmin rights and test them before removing builtin\administrators.