Article ID: 263712
Article Last Modified on 4/7/2006
APPLIES TO
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 7.0 Enterprise Edition
- Microsoft SQL Server 2005 Enterprise Edition
This article was previously published under Q263712
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 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 not want to permit Microsoft Windows system administrators to have 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.
For additional information, click the following article number to view the article 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 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 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 limit access for Microsoft Windows NT Administrators as system administrators on a SQL Server cluster, follow these steps:
- 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.
- 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 limit access for Windows NT Administrators as system administrators on a SQL Server cluster, follow these steps:
- 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.
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'- Remove the BUILTIN\Administrators login from SQL Server after you install the virtual server.
Keywords: kbinfo kbsql2005cluster KB263712
