Solutions for beginners who are rejected by SQL Server

Q: I have locked myself out of SQL Server and cannot Log On As sysadmin. Is there any solution? Do I need to reinstall SQL Server?

A: you do not have to reinstall SQL Server. To re-access SQL Server, you must modify the registry key value of SQL Server 2000 and SQL Server 7.0 to determine the Authentication Mode of SQL Server.

In SQL Server 7.0, the key is:

HKEY_LOCAL_MACHINESOFTWAREMicrosoft

MSSQLServerMSSQLServerLoginMode

In SQL Server 2000, the key is:

HKEY_LOCAL_MACHINESOFTWAREMicrosoft

MicrosoftSQLServerMSSQLServerLoginMode

If the value of "LoginMode" is 0, only Windows authentication is performed. If the value is 1, the authentication mode is mixed. After you shut yourself out, you can change LoginMode to 1, restart SQL Server, Log On As sa (system administrator), and enter the sa password you know.

The following information about SQL Server role members may help you understand why you shut yourself out of SQL Server. When You Install SQL Server 2000 or 7.0, the installation process is automatically BUILTINAdministrators. Create a Logon account that is a member of the sysadmin role. "BUILTINAdministrators" Logon account represents Microsoft Windows®2000 or Microsoft Windows NT®Server. The "Administrator" account of Windows 2000 or Windows NT is a member of the local system Administrator group. In addition, if your server is a member of a Domain (including a Windows 2000 Domain and a Windows NT 4.0 Domain), The Domain Admins global group will also become a member of the local system administrator group. This means that all members in the Local Group of the system administrator will automatically obtain the "sysadmin" permission on SQL Server.

To enhance the security of your SQL Server, you may prefer to create your own group and grant it sysadmin permissions, and then delete the BUILTINAdministrators Logon account, or at least from sysadmin, log on to SQL Server as sysadmin, unless you modify the registry key value as described above. The correct order is:

Create a Windows 2000 or Windows NT user group and assign members to the group. For example, create a group called SQLAdmins.

Map SQLAdmins to an account in SQL Server that authenticates and logs on using Windows authentication, and assign the account to the sysadmin Server role.

Delete the BUILTINAdministrators Logon account or delete it from the sysadmin server role.

Change the Authentication Mode of SQL Server to "Windows authentication only ".

Restart SQL Server to reflect changes in Authentication mode.

Note: If you follow these steps in this wrong order: Delete the BUILTINAdministrators Logon account and change the SQL Server Authentication Mode to "Windows authentication only ", then restart SQL Server. Then, the sa account will be disabled and cannot enter SQL Server because no other Windows Authentication Logon account is defined. To avoid this, implement these security measures in the correct order.