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

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

By default, SQL Server 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:

On a SQL Server cluster, you can remove the BUILTIN\Administrators group if the following conditions are true:

• Before you run the Cluster Wizard, 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.
  
  For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  291255  (http://support.microsoft.com/kb/291255/ ) BUG: IsAlive check does not run under the context of the BUILTIN\Administrators account in SQL Server 2000 Enterprise Edition
• Before you run the Cluster Wizard, you must create the service accounts for SQL Server Agent and SQL Server and make these accounts members of the Sysadmin fixed server role.
• If the SQL Server cluster is on a computer that is running Microsoft Windows 2000 and you are running a full-text search, 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: