4

I'm locked out of a local database, and the single-user mode option that has worked for me in the past isn't working for me here. I could use some options to try.

It's saying the account is disabled, which I didn't think was even possible for single-user mode.

What I've tried already:

Restarting in single-user mode:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>net stop MSSQL$DBNAME
The SQL Server (DBNAME) service is stopping..
The SQL Server (DBNAME) service was stopped successfully.


C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>.\sqlservr -sDBNAME -m -c

[seems to come up]

The login attempt looks like this:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>sqlcmd -S .\DBNAME
Msg 18470, Level 14, State 1, Server MY-PC\DBNAME, Line 1
Login failed for user 'My-PC\sarahg'. Reason: The account is disabled.

Server output:

2012-05-29 15:55:25.11 Logon       Error: 18470, Severity: 14, State: 1.
2012-05-29 15:55:25.11 Logon       Login failed for user 'My-PC\sarahg'. Reason: The account is disabled. [CLIENT: <local machine>]

Restarting in DAC mode:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>.\sqlservr -sDBNAME -m -c -t 7806
... blah blah ...
2012-05-29 15:59:05.59 spid6s      Server name is 'MY-PC\DBNAME'. This is an informational message only. No user action is required.
2012-05-29 15:59:05.60 spid9s      Starting up database 'model'.
2012-05-29 15:59:05.60 spid6s      Informational: No full-text supported languages found.
2012-05-29 15:59:05.60 spid6s      Starting up database 'msdb'.
2012-05-29 15:59:05.72 Server      A self-generated certificate was successfully loaded for encryption.
2012-05-29 15:59:05.72 Server      Server is listening on [ 'any' <ipv6> 49201].
2012-05-29 15:59:05.74 Server      Server is listening on [ 'any' <ipv4> 49201].
2012-05-29 15:59:05.74 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\DBNAME ].
2012-05-29 15:59:05.74 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$DBNAME\sql\query ].

The login attempt looks like this:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>sqlcmd -A -S .\DBNAME
Msg 18470, Level 14, State 1, Server MY-PC\DBNAME, Line 1
Login failed for user 'Mys-PC\sarahg'. Reason: The account is disabled.

With server log:

2012-05-29 16:00:24.86 Logon       Error: 18470, Severity: 14, State: 1.
2012-05-29 16:00:24.86 Logon       Login failed for user 'Mys-PC\sarahg'. Reason: The account is disabled. [CLIENT: 127.0.0.1]

Any help is very welcome,

-- Sarah

sarahg
  • 41
  • 1
  • 1
  • 3

3 Answers3

6

I have had the exact same problem and tried this solution and it works: Using PSExec (a free download from Microsoft) to connect to an instance using the NT AUTHORITY\SYSTEM account. Sample syntax:

PsExec -s -i "C:\...\Tools\Binn\ManagementStudio\Ssms.exe"

This will launch Management Studio as a Windows account that has native sysadmin permissions, and this will allow you to switch to mixed mode, enable the sa account, reset the sa account, add your own Windows account as an administrator, etc.

A lot more information can be found in the following article:

http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/

However bear in mind that you may be breaking some contract, just wanted to note it. :)

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
user30928
  • 61
  • 1
  • 2
1

To get into SQL in this case

  1. Start the instance of SQL Server in single-user mode by using either the -m or -f options.
  2. Members of the Windows Administrators group now have access to SQL Server when SQL Server is in started in single-user mode, also known as “maintenance mode".
  3. Stop the SQL Server Agent service. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user
  4. net stop MSSQLSERVER (just like you did)
  5. Start server in single user mode

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>sqlservr -m

  1. Login to your server using sqlcmd -S <Servername>
  2. you will get prompt 1>

  3. Type in

    EXEC sp_addsrvrolemember 'MachineName\AWindowsuser', 'sysadmin'; GO

chose any windows user you want to be in the sysadmin group.

  1. Stop the running sql server
  2. Login to your windows machine with the user that you gave access to the sysadmin group
  3. Start the mssqlserver service net start MSSQLSERVER
  4. IN the SSMS chose windows quthentication mode when connecting to SQL
  5. you can then go to Server>Security>Logins>sa and change the password and enable the account
AmmarR
  • 2,806
  • 3
  • 25
  • 32
1

Are you a member of the local administrators group? running net localgroup administrators at the command prompt, does it include sarahg? Only members of administrators can log in with -m, as described in Connect to SQL Server When System Administrators Are Locked Out.

If you are a local administrator then make sure you connect to the -m started instance from an elevated process (started with right click on icon/Run As Administrator...). This includes connecting from command line (sqlcmd) or from SSMS. See How to use User Account Control (UAC).

If you are a local administrator, are connecting from an elevated process and still getting access denied because of explicitly disabled login (I can't remember if it can actually happen) then simply create another local login, add it to local administrators then connect with that login:

c:\>net user /add atemplogin atempapassword
c:\>net localgroup administrators /add atemplogin
c:\>runas /user:atemplogin sqlcmd -E
>alter login [My-PC\sarahg] enable;
>go
Remus Rusanu
  • 51,846
  • 4
  • 95
  • 172