Security Issues with the SQL Server BUILTIN Administrators Group
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.
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?
- 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.
- Validate other Windows groups or Windows logins are assigned SQL Server System Administrator rights on this SQL Server.
- Review the rights assigned to the BUILTIN\Administrators group.
- Research the members of the Windows Local Administrators group.
- 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.
- 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.
- 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.
- 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?
|SQL Server 2000
exec sp_revokelogin N'BUILTIN\Administrators'
|SQL Server 2000
|SQL Server 2005
|DROP LOGIN [BUILTIN\Administrators]
|SQL Server 2005
- 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...
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips