Free SQL Server Learning - Making the most out of SQL Server Agent
solving sql server problems for millions of dbas and developers since 2006



SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups SQL Server Events I am MSSQLTips MSSQLTips Advertising Options

MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page









Top SQL Server Tools






































Security Issues with the SQL Server BUILTIN Administrators Group

By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 3 | More > Security

Problem
The principal of least privileges is a cornerstone to most security implementations.  The premise behind the principal is to only grant users, developers, DBAs, network administrators, etc. the needed rights and nothing more.  If additional rights are required, evaluate the rights and grant the access accordingly.

With SQL Server 2000 and 2005 one area that does not seem to follow this principal is related to the default rights for the BUILTIN\Administrators group.  By default this group has SQL Server System Administrator rights to SQL Server when it is installed.  The same level of default rights are also granted to BUILTIN\Administrators group in SQL Server 2005 during the installation.  What this means is that any account in the Windows Local Administrators group has SQL Server System Administrator rights.

Solution
The BUILTIN\Administrators can easily be removed from SQL Server to prevent this security issue, but heed the warnings below prior to removing the group from SQL Server.

What steps should I take prior to considering removing this group?

  1. Verify that you know the "sa" password by logging into the SQL Server with the "sa" account with either Query Analyzer or Management Studio on the SQL Server you want to modify.
  2. Validate other Windows groups or Windows logins are assigned SQL Server System Administrator rights on this SQL Server.
  3. Review the rights assigned to the BUILTIN\Administrators group.
  4. Research the members of the Windows Local Administrators group.
  5. Figure out if an additional group should be created in Active Directory and assigned rights in SQL Server or if specific logins should be assigned rights to SQL Server.
  6. If necessary, create the additional logins and groups in Active Directory and assign rights in SQL Server to ensure a minimum of 1 login and/or the "sa" login has SQL Server System Administrator rights.
  7. Validate that the members of the BUILTIN\Administrators group do not own any databases, objects, Jobs, etc and that none of the logins are connected to SQL Server.
  8. If any of these steps were not completed, repeat the needed steps.  If all of these steps have been followed and you are confident that removing the BUILTIN\Administrators group will not cause any issues proceed to the next set of directions.

*** NOTE *** - Do not remove the BUILTIN\Administrators group from SQL Server if other logins or groups do not have SQL Server System Administrator rights or if you do not know the "sa" password.

With all of that being said, how do I remove the BUILTIN\Administrators group?

Method Directions
SQL Server 2000
T-SQL Commands		 USE MASTER
GO
exec sp_revokelogin N'BUILTIN\Administrators'
GO
 

OR

SQL Server 2000
Enterprise Manager
  1. Open Enterprise Manager.
  2. Navigate to the Security folder.
  3. Double click on the Logins icon, which will load the SQL Server logins and groups in the right pane.
  4. Locate the BUILTIN\Administrators group.
  5. Right click on the BUILTIN\Administrators group and select the 'Delete' option.
  6. On the subsequent screen, read the message and if you agree press 'Yes' to remove the group.
  7. Refresh the pane to verify the group has been dropped.
 
Method Directions
SQL Server 2005   
T-SQL Commands		 DROP LOGIN [BUILTIN\Administrators]

OR

SQL Server 2005
Management Studio
  1. Open Management Studio.
  2. Navigate to the Security folder.
  3. Expand (+) the Logins folder, which will load the SQL Server logins and groups in the right pane.
  4. Locate the BUILTIN\Administrators group.
  5. Right click on the BUILTIN\Administrators group and select the 'Delete' option.
  6. On the subsequent screen, review the screen and if you agree press 'OK' to remove the login.
  7. Refresh the pane to verify the login has been dropped.

Next Steps

  • Review some of your key SQL Servers to determine if the BUILTIN\Administrators Group has the default System Administrator rights in SQL Server.
  • Talk with your team members about this security change and others to ensure these types of changes make sense in your environment.
  • Work towards building a solidified process to secure your SQL Servers as you deploy them to meet the organizational needs.
  • Stay tuned for more simple steps to secure your SQL Servers...


Last Update: 7/31/2006

About the author



Print  
Become a paid author


Comments and Feedback:

Wednesday, January 20, 2010 - 9:16:29 PM - bedwar2 Read The Tip

The problem not mentined in the article (I know it's about 4 years old now) is that removing that group makes the SQL Server Agent service fail. I guess you have to create an account with a non-expiring password in order for that to work. Fix one problem, though and cause another. Sometimes I have to ask do you want it secure or do you want it to work?


Friday, January 22, 2010 - 4:08:48 PM - admin Read The Tip

Bedwar2,

Thank you for the feedback.  I think the situation you mentioned can be alleviated by granting the “DBA” Windows domain group SQL Server sysadmin rights before making any changes to the BUILTIN\Administrators Group rights in SQL Server.   In addition, setup the SQL Server services to execute with a Windows domain account which has SQL Server sysadmin rights before making any changes to the BUILTIN\Administrators Group rights in SQL Server.   As a last preventive measure, be sure you know the sa password and validate you are able to access the SQL Server before making any changes to the BUILTIN\Administrators Group rights in SQL Server .  Hopefully this will prevent any sort of permissions issues.

Thank you,
The MSSQLTips Team

 

Friday, November 12, 2010 - 3:14:35 PM - krystian Read The Tip

DO NOT REMOVE BUILTIN\Administrators Group from SQL server when you planning to install cluster.

If you do will NOT work

Friday, April 29, 2011 - 8:52:56 AM - Dario Read The Tip

Porque cuando quito el permiso de BULTIN no me hace los backup en el server programados

Friday, April 29, 2011 - 9:35:07 AM - Greg Robidoux Read The Tip

Dario,

You should look at the service account that you are using for SQL Agent.  If the account was using the BUILTIN\Administrators group to get access to SQL Server then you will have issues running jobs.

Use SQL Server Configration Manager to look at your service accounts and make sure that account has the access it needs within SQL Server to perform backups along with the necessary permissions on the file system to write the backup files. 

Tuesday, May 14, 2013 - 9:19:57 AM - user1 Read The Tip

BUILTIN\Administrators is suddnely using 1.7 Terabytes on my personal computer. I am the only user and I am using less than 150 gigs. I would like to delete whatever is being saved without damaging the PC. Thanks for your help.

 


Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter

Comments
*Enter Code refresh code


 

Sponsor Information
"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen."

Get your SQL Server database under version control now! Find out why...

Is “blocking” a bad word at your company? Contact the Edgewood SQL Server Consultants for the resolution.

Unlock the power of the Transaction log to discover unauthorized changes and recover lost data

SQL Server Data Tools - Got questions? Get the answers here!

Copyright (c) 2006-2013 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups
Some names and products listed are the registered trademarks of their respective owners.

Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com