• 0

SQL Server 2008 R2 - Administrator Locked Out


Question

last week i managed to lock myself out of SSMS :pinch:

so, after doing some searches on Google, i found these sites which seemed promising:

http://www.bandgap.cs.rice.edu/classes/comp410/resources/Using%20SQL%20Server/How%20to%20regain%20access%20if%20you%27ve%20been%20locked%20out.aspx

http://beyondrelational.com/blogs/chintak/archive/2010/07/20/sql-2008-r2-new-installation-and-login-password-unknown.aspx

http://msdn.microsoft.com/en-us/library/dd207004%28v=sql.110%29.aspx

I found this on the MSDN site, linked above: "Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role." Neither the -m or -f work, btw.

None of these sites have worked for me. I've now tried to use this "-m" technique through cmd as well as the SQL Server Configuration Manager. After making the change, i restart the SQL Server service as well as the SQL Server Browser service. i then reopen SSMS and try to login using the admin account and it still tells me that the account is locked. Since there is another Administrator on this server, i logged in to Windows as this admin, then tried to open SSMS... same result - says that the account doesnt exist. So, clearly, this "-m" switch isnt working.

Does anyone have another workaround? All I need is to re-enable the administrator account again.

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

Try this method

Overview

System.Data.SqlClient.SqlError: Login failed for user ?sa? because the account is currently locked out. The system administrator can unlock it. The error occurs when the SQL Server administrator account is locked. To unlock the SQL Server ?sa? account, run the following command:

Solution

ALTER LOGIN sa WITH PASSWORD = 'new_password' UNLOCK

*Enter the existing ?sa? password if remembered, otherwise enter a new one.

Option 1 ?From MicroSoft SQL Server Management Studio

You will need authority to access the SQL Server directly. Choose Widows Authentication mode and login with an administrator account. Open a new query window, enter the ALTER command and click execute to unlock the ?sa? account.

Option 2 ?From the command prompt

Using the Windows ?Trusted? Connection

SQL Server 2000

OSQL -S SERVER\SQL Instance -E

SQL Server 2005\2008

SQLCMD -S SERVER\SQL Instance -E

(Using a SQL administrator account)

SQLCMD -S SERVER\SQL Instance -U account name -P Password

Once connected to the SQL Server you will see the 1> prompt. You can type the ALTER DATABASE command and hit Enter. You will see the 2> prompt, type GO and hit Enter to execute the command. Type Exit to log out and close the command prompt.

Link to comment
Share on other sites

  • 0

Thanks, but Option 1 doesnt work since i cant connect to SSMS.

Option 2 doesnt make much sense to me - can you explain? Do i run "SQLCMD -S SERVER\SQL Instance -E" 1st, then "SQLCMD -S SERVER\SQL Instance -U account name -P Password" 2nd?

Also, when i try to run SQLCMD, i get another error:

post-34502-0-32006800-1330980559.jpg

looks like i have another situation to fix first, eh?

update: i looked up what those commands do. so when i try either - i get "login failed, account disabled."

so doesnt seem that route will work either...

Link to comment
Share on other sites

  • 0

thanks Romtec. that got me a step in the right direction, but im still stuck.

I removed the "-m" in Configuration Manager, then changed the registry value to allow Mixed Mode logins.

from there i was able to login using a SQL Auth user; however, that user has no permissions to alter the Master DB. so im still stuck - i cant enable any admin account.

Link to comment
Share on other sites

  • 0

ok i finally got it to work! Thanks Romtec for all your help!

Here's what i had to do:

  • Removed the "single-user mode" switch in Configuration Manager using "-m" on the Advanced tab of the SQL Server service - restart the service (and SQL Server Browser service)
  • Changed the registry setting from 1 to 2: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\<your instance>\LoginMode
  • Login to Windows as another Administrator (since the 1st admin was locked out of the Database Engine)
  • run CMD as Administrator, navigate to the directory where SQLCMD is installed (in my case: D:\Program Files\Microsoft SQL Server\100\Tools\Binn)
  • run the command "SQLCMD -S <server name>\<instance name> -E" <enter>
  • If successful, the window should simply display a "1>"
  • Type 'ALTER LOGIN sa WITH PASSWORD 'new_password' ENABLE (this enables the sa account, which is disabled by default)
  • at the "2>" prompt, type the command "GO 1" If successful, you'll see another "1>". Simply type 'exit' to leave the SQLCMD program.
  • Login to Windows again as the previously-locked-out Administrator
  • Open SSMS using the 'sa' account and SQL Authentication
  • Under Security - Logins, re-enable the locked Administrator account

Done :)

Link to comment
Share on other sites

This topic is now closed to further replies.