Locked Out of SQL Server
/>
Q
I locked myself out
of SQL Server without being able to log on with
a sysadmin login. Can I fix this, or do I need
to reinstall SQL Server?
/>A
You don't have to
reinstall SQL Server. To access SQL Server, you
need to use the registry key for SQL Server 2000
and SQL Server 7.0 that determines the
authentication mode of SQL Server.
/>
In SQL Server 7.0, the key
is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft
\MSSQLServer\MSSQLServer\LoginMode
/>
In SQL Server 2000, the key
is:
/>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\
/>MicrosoftSQLServer\<instance_name>\MSS
QLServer\LoginMode
The value of
LoginMode is 1 for Windows Authentication only,
and 2 for Mixed Mode. After locking yourself
out, you can change the value of LoginMode to 2,
restart SQL Server, and log in as the system
administrator (sa), provided you know the sa
password.
The following information
about SQL Server role memberships might help you
understand how you locked yourself out. When you
install SQL Server 2000 or 7.0, the installation
process automatically creates a login for
BUILTIN\Administrators with sysadmin server
role membership. The BUILTIN\Administrators
login stands for the Administrators local group
in your Microsoft Windows® 2000 or Microsoft
Windows NT® server. The Windows 2000 or Windows
NT Administrator account is a member of the
Administrators local group by default. Also, if
your server is a member of a domain (both in
Windows 2000 and in Windows NT 4.0), the global
group Domain Admins becomes a member of the
local Administrators group, too. This means that
all members of the Administrators local group
automatically gain sysadmin rights in your SQL
Server.
To tighten security for your
SQL Server, you might prefer to create your own
group and map it to a login with sysadmin rights
to your SQL Server computer and remove the
BUILTIN\Administrators login, or at least
remove it from the sysadmin server role. This
way, you'll have better control of who
gains sysadmin rights in your SQL Server. This
approach also breaks the relationship between
SQL Server administrators and Windows 2000 or
Windows NT administrators, who usually have
different tasks and need different rights. As
another step in tightening security, you might
want to configure your SQL Server to support
only Windows Authentication. However, keep in
mind that this configuration will disable your
sa account. (This method is probably the only
way to disable the sa account because you
can't delete the sa account.) If you
implement these security measures in the wrong
order, you won't be able to log on to SQL
Server as a sysadmin without using the key I
described. The correct order is:
/>1.
Create the Windows 2000 or
Windows NT group and assign members to it. For
example, call this group
"SQLAdmins".
2.
/>Map SQLAdmins to a Windows Authenticated login
in your SQL Server and assign this login to the
sysadmin server role.
3.
/>Delete the BUILTIN\Administrators login or
remove it from the sysadmin server role.
/>
4.
Change your SQL Server
authentication mode to Windows Authentication
only.
5.
Restart SQL
Server to reflect the changed authentication
mode.
Note: If you implement these
security steps in the wrong order-by deleting
the BUILTIN\Administrators login, changing your
SQL Server authentication mode to Windows
Authentication only, then restarting SQL Server,
you will have disabled the sa account and you
will have no other Windows Authenticated login
defined in your SQL Server. Thus, you are locked
out. To avoid this situation, implement the
security measures in the correct order.
/>