SQL Server: How to gain access back onto the SQL instance in case you get completely locked out of the instance with no sysadmin access available.

In the month of February, during early morning hours, one of our DBA(s) mistakenly ran a script on a remote SQL instance, APPROVAL environment, which deleted all logins and groups from the instance concerned.

The SQL instance was configured under windows authentication, not mixed and hence sa was also disabled. “BUILTIN\Administrators” group also got dropped as part of the script. This meant that now there was no valid login on the SQL instance, no login using which we could connect to the SQL instance.

Applications start failing, as they are now not able to connect to the instance with the logins gone.

A very messy situation to be in as a DBA. We only had admin access to the physical box on which the SQL instance was running.

This being SQL 2005, we had only 1 option to gain access back to the SQL instance.

As part of regular overnight maintenance activities, we had the full backup of named APPROVAL instance XYZ’s master database available.

  1. Named Approval XYZ instance shares node XXABCSQLnn with other instances ABC, DEF and GHI.
  2. Restore APPROVAL XYZ’s master database as a simple user database on another APPROVAL instance e.g. ABC. Care to be taken to ensure that restored physical file names are changed to xxxxx_res.mdf and xxxxx_res.ldf
  3. De-attach the restored user database.
  4. Shutdown Approval XYZ SQL services
  5. Rename the original .mdf and .ldf files of APPROVAL XYZ SQL Service to xxxx_old.mdf and xxxx_old.mdf at the correct location where the system database files for XYZ instance resides.
  6. Copy the restored to xxxxx_res.mdf and xxxxx_res.ldf to the correct location where the system database files for APPROVAL XYZ instance resides.
  7. Rename the copied files to the correct name master.mdf and mastlog.ldf in the location where the system database files for APPROVAL XYZ instance resides
  8. Bring up the APPROVAL XYZ Service.
  9. All logins should be back.

Moral of the story: ensure you have a robust backup process, inclusive for system databases, in place for all SQL instances you are responsible for.

   

For SQL 2008, R2 and above, BOL mentions a sort of backdoor entry in case members of sysadmin group gets locked out. I have reproduced the steps here for the reader’s benefit.

  1. Using –m or –f option, start the SQL instance in a single user mode
  2. Using sqlcmd, connect to the instance and then create the desired login.
  3. Note: ensure you first stop the SQL Agent service. Otherwise the SQL Agent might first connect to the instance using up the single user connection thereby preventing us to connect.
  4. After creation of the desired login, grant the login membership to sysadmin role.
  5. Under single user mode, any member of the system’s “Local Administrators” group can connect to the SQL instance as a member of the sysadmin fixed role.
  6. Disconnect, revert the start up parameters and then restart the SQL instance.

Sysadmin access now available to the SQL instance. But the drawback, all other logins that got deleted needs to be recreated and mapped correctly to allow user and application access. A huge task.

 

Regards

Vasudev Menon

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook

   

One Comment on “SQL Server: How to gain access back onto the SQL instance in case you get completely locked out of the instance with no sysadmin access available.”

  1. Your comment “For SQL 2008, R2 and above, BOL mentions a sort of backdoor entry in case members of sysadmin group gets locked out.” gives a false impression that this mechanism is available from SQL 2008 R2 only. If you are not aware, be informed that this method is available in SQL server 2005 too.

Leave a Reply

Your email address will not be published.