Restoring Access to Microsoft SQL Server

by Dmitry Kirsanov 26. August 2013 05:29

As a note to system administrator – what to do, when your one and only administrators account is locked on your SQL Server.

You need to perform 5 quick steps.

  1. Login to your windows server. Run command prompt as Administrator.
  2. Use command net stop mssqlserver to stop your SQL Server service.
  3. Open Command Prompt using “Run As Administrator” mode. Navigate to the directory of your SQL Server executable file (sqlservr.exe) and run it using –m switch, like that: sqlservr –m”sqlcmd” – it will run your SQL Server in single user mode, specifying that connection will only be granted to sqlcmd program. Without specifying the program name, your single connection could be quickly taken over by whatever application is accessing your SQL Server.
  4. From the command prompt, run the sqlcmd program using the Single Mode switch:
    sqlcmd –s .
    Note that dot in the end – this means you are connecting to local SQL server.
  5. Now you can login even if your SQL user account is locked. There are few commands you may find useful in order to grant access to the server:
    1. EXEC sp_addsrvrolemember 'CONTOSO\Buck', 'sysadmin';
      This command adds Active Directory user ‘Contoso\Buck’ to the security role ‘sysadmin’, which grants this user full control over SQL Server.
    2. ALTER LOGIN sa ENABLE;
      Use this command to enable the disabled sa account. Note, however, that you may need to change the password first, or ensure that this account doesn’t require strong password, as this could be the issue.
    3. ALTER LOGIN Mary5 WITH PASSWORD = 'YourStrongPasswordHere';
      This will change password for user Mary5 to whatever you want.
  6. Restart your SQL Server service – either through the Control Panel \ Administrative Tools \ Services, or by issuing two commands: net stop mssqlserver and net start mssqlserver.

After that, your connection to SQL Server should be restored.

blog comments powered by Disqus