Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Removing the Builtin Administrators - Some Pitfalls to Avoid

By Kathi Kellenberger, (first published: 2005/01/12)

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

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

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.

Total article views: 29430 | Views in the last 30 days: 8
 
Related Articles
FORUM

BUILTIN\Administrators account overrides the permissions

BUILTIN\Administrators account overrides the permissions of all security accounts

FORUM

REMOVE BUILTIN\ADMINISTRATORS after installing CLUSTER....

HELP ME: REMOVE BUILTIN\ADMINISTRATORS after installing CLUSTER....

FORUM

What's the different between sql server accounts NT AUTHORITY\SYSTEM and BUILTIN\Administrators?

What's the different between sql server accounts NT AUTHORITY\SYSTEM and BUILTIN\Administrators?

FORUM

Removed builtin administrators group now have spurious backup failures

Removed builtin administrators group now have spurious backup failures

FORUM

Builtin\Administrators Needs to be added

Builtin\Administrators Needs to be added

Tags
security    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones