This article describes how to disable Microsoft Windows NT System Administrators from having System Administrator (SA) permissions on SQL Server. By default, the Microsoft SQL Server installer creates a "builtin/administrators" Logon, which grants system administrator (SA) permissions to any account in the local administrator group.
In some cases, you may not want the Microsoft Windows NT System Administrator to have such access permissions on SQL Server.
On an independent server running SQL Server, you can delete "builtin/administrators" Logon from SQL Server to restrict such access permissions.
For other information, click the following article number to view the article in the Microsoft Knowledge Base:
237604 (http://support.microsoft.com/kb/237604/EN-US/) PRB: SQL Server Agent cannot be started and error 18456 291255 (http://support.microsoft.com/kb/291255/EN-US/) BUG: isalive check is not running 295034 (http://support.microsoft.com/kb/295034/EN-US/) fix in the context of the builtin/Administrators account: if builtin/administrators logon is deleted, Microsoft Search Service may cause CPU usage to reach 100% 274446 (http://support.microsoft.com/kb/274446/EN-US/) INF: upgrade to the SQL Server 2000 failover solution recommended for all non-SQL Server 2000 virtual servers
Deleting "builtin/administrators" on the cluster SQL Server may cause problems. If you delete the "builtin/administrators" logon to the cluster server, the SQL server resource will be suspended online and will eventually fail. However, if you run the "failover cluster wizard" after deleting the "builtin/administrators" Logon, the process will be successfully completed, because the "failover cluster wizard" will explicitly add an SQL Server login for the account that requires access permissions.
SQL Server 6.5 and SQL Server 7.0
To restrict the Windows NT administrator access permission to the system administrator (SA) on the SQL server cluster, perform the following steps:
- Explicitly add an account that is being used for cluster service as an SQL Server logon. You must assign a system administrator role for this logon.
Note:If the server running SQL Server is detached from the cluster and then joins the cluster, you must repeat this process.
- After successfully installing and adding SQL Server to the cluster in the SQL Server failover cluster wizard, delete "builtin/administrators" from SQL Server to log on.
SQL Server 2000
To restrict the Windows NT administrator access permission to the system administrator (SA) on the SQL server cluster, perform the following steps:
- Explicitly Add the account being used for cluster service as SQL Server logon. You must assign a system administrator role for this logon. To use full-text search for a cluster, you must add the [nt authority/system] account to the server.SysAdminGroup. For example:
Grant [NT Authority/System] a logon 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'
- After the virtual server is installed, delete "builtin/administrators" from SQL Server to log on.