Locked out of SQL Server?

June 8, 2004 · 10 Comment s

A month or two back I helped out a friend who had inadvertently locked themselves out of SQL Server. Basically, my friend was trying to lock down SQL Server to make it more secure and so removed the Windows group 'BUILTIN\Administrators' from the sysadmins group in SQL Server and also changed the Authentication method from Mixed Mode to Windows Authentication. Removing the BUILTIN\Administrators group isn't a problem as you can still access SQL Server using the sa account ... except you just changed authentication mode, which locks out the sa account - whoops! So, how do you get around this? Well, the first thing you need to do is Re-install SQL Server ... nah! only kindding :) The key to getting back in is to modify the following registry key: In SQL Server 7.0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft \MSSQLServer\MSSQLServer\LoginMode
And in SQL Server 2000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\
MicrosoftSQLServer\<instance_name>\MSSQLServer\LoginMode
The value of LoginMode is 1 for Windows Authentication and 2 for Mixed Mode. So change the value to 2, restart SQL Server and log back in using the sa account. OK, so you're back in but you'd still like to tighten things down, by using Windows Authentication. Well here's how to do it. 1) Create a 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 (if you haven't already done so). 4) Change your SQL Server authentication mode to Windows Authentication only. 5) Restart SQL Server to reflect the changed authentication mode.

Tags: SQL Server

10 response s so far ↓

Leave a Comment

Leave this field empty: