How to impede Windows NT administrators from administering a clustered instance of SQL Server

Article translations Article translations
Article ID: 263712 - View products that this article applies to.
This article was previously published under Q263712
Expand all | Collapse all

On This Page

SUMMARY

This article explains how you can impede Microsoft Windows system administrators from having system administrator permissions in Microsoft SQL Server, Enterprise Edition.

MORE INFORMATION

By default, SQL Server 2005 and earlier Setup creates the BUILTIN\Administrators login, and then adds the login to the "Sysadmin" fixed server role. This change grants system administrator permissions to any account in the Local Administrators group. In some environments, you might want to impede Microsoft Windows system administrators from having this kind of access to SQL Server. On a stand-alone computer that is running SQL Server, you can remove the BUILTIN\Administrators login from SQL Server and limit this type of access. Before you remove BUILTIN\Administrators from the sysadmin role, make sure that at least one other account is a member of the sysadmin role.

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
237604 SQL Server Agent does not start and displays error 18456
291255 BUG: IsAlive check does not run under the context of the BUILTIN\Administrators account in SQL Server 2000 Enterprise Edition
295034 FIX: Microsoft Search Service may cause 100% CPU usage if BUILTIN\Administrators login is removed
274446 Upgrade to SQL Server 2000 failover solution recommended for all non-SQL Server 2000 virtual servers


On a SQL Server cluster, you can remove the BUILTIN\Administrators group from the syadmin role or remove it completely from the logins if the following conditions are true:
  • You must create the cluster service account as a login in SQL Server. This account does not need to be a member of the "Sysadmin" fixed server role. This account just needs to be able to connect and to do the "IsAlive" check which requires membership in the public role in the master database. For SQL Server 7.0 this must be done before you run the "Cluster Wizard". For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
    291255 IsAlive check does not run under the context of the BUILTIN\Administrators account in SQL Server 2000 Enterprise Edition
  • You must create logins for the service accounts used to start SQL Server Agent and SQL Server and make these accounts members of the Sysadmin fixed server role. For SQL Server 7.0 this must be done before you run the "Cluster Wizard".
  • If the SQL Server cluster is on a computer that is running Microsoft Windows 2000 and you are running the Full-Text Search service, you can remove the BUILTIN\Administrators group if the NT Authority\System account is a member of the Sysadmin fixed server role.
Note The Cluster Wizard only exists in SQL Server 7.0. Therefore, if you are using SQL Server 2000 or SQL Server 2005, ignore the references to the Cluster Wizard in these conditions.

SQL Server 6.5 and SQL Server 7.0

To impede access for Microsoft Windows NT Administrators as system administrators on a SQL Server cluster, follow these steps:
  1. Explicitly add the account that is being used for the cluster service as a SQL Server login. You must assign the "Sysadmin" role to this login.

    Note If the computer that is running SQL Server is unclustered and then reclustered, you must repeat this process.
  2. Remove the BUILTIN\Administrators login from the computer that is running SQL Server after the SQL Server Failover Cluster Wizard successfully clusters the SQL Server installation.

SQL Server 2000 and SQL Server 2005

To impede access for Windows NT Administrators as system administrators on a SQL Server cluster, follow these steps:
  1. Explicitly add the account that is being used for the cluster service as a SQL Server login. You do not need to assign the "Sysadmin" role to this login.
  2. Make sure that there is at least one other account that is a member of the sysadmin role in SQL Server.
  3. If a full-text search will be used on the cluster, you must add the [NT Authority\System] account to the server's "sysadmin" group. For example, you can use the following sample code:
    Grant [NT Authority\System] a login to SQL Server:
    EXEC sp_grantlogin [NT Authority\System]
    Add that account to the sysadmins role:
    EXEC sp_addsrvrolemember @loginame = [NT Authority\System] 
    , @rolename =  'sysadmin'
    					
  4. Remove the BUILTIN\Administrators login from SQL Server after you install the virtual server.
For more information about instances of SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base:
932881 How to make unwanted access to SQL Server 2005 by an operating system administrator more difficult

Properties

Article ID: 263712 - Last Review: September 15, 2009 - Revision: 10.0
APPLIES TO
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 7.0 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise Edition
Keywords: 
kbsql2005cluster kbinfo KB263712

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com