Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
is there a chance to login with another user like 'sa' and enable your account, since its local as you mentioned –  AmmarR May 29 '12 at 8:08
    
I don't know the password for any other user, including SA. Is there a default? –  sarahg Jun 11 '12 at 0:46
    
This sounds like your Windows account is locked out. Use the User Account Manager in Windows to unlock the account, then try accessing SQL Server. –  Max Vernon Sep 7 '12 at 20:13
add comment

3 Answers

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. :)

share|improve this answer
add comment

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
share|improve this answer
    
I think the sqlagent thing is the only part that's different to what I've tried, and I get: C:\Users\sarahg>net stop SQLAgent$DBNAME The SQL Server Agent (DBNAME) service is not started. –  sarahg Jun 15 '12 at 1:27
    
I'm happy to paste full logs somewhere but this comment thing seems to have a character limit. –  sarahg Jun 15 '12 at 1:29
    
Hrm I'm getting a new set of errors now when I try to start the db in single-user mode. I'll see if I can make sense of those & if not post I'll it here. –  sarahg Jun 15 '12 at 2:01
    
Use pastebin to post long errors logs and keep the link here –  AmmarR Jun 15 '12 at 22:09
add comment

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
share|improve this answer
    
>net localgroup administrators Alias name administrators Comment Administrators have complete and unrestricted access to the computer/domain Members ------------------------------------------------------------------------------- Administrator sarahg The command completed successfully. –  sarahg Jun 15 '12 at 1:31
    
Still working through the process; starting the db in single-user mode isn't working for me at all right now. Restarting the db normally, and trying your add user commands, I get: Msg 18456, Level 14, State 1, Server SARAHS-PC\BPSINSTANCE, Line 1 Login failed for user 'Sarahs-PC\atemplogin'. –  sarahg Jun 15 '12 at 1:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.