Removing builtin\administrators: Some pitfalls to avoid

Introduction

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.

Oops!

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.

Conclusions

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.