Tips & Tricks: YOU HAVE LOST ACCESS TO SQL SERVER. NOW WHAT?


A Principal Data Engineer at Microsoft (Saleem Hakani) had authored the below SQL Server Tips & Trick and I thought it would be one that would be found handy by many. Keep this one handy you never know when you may need it Smile (Hopefully not too many times).

You are working as a trusted DBA responsible for some extremely important SQL Servers for your company. For the sake of security, you have performed the following steps to secure SQL Servers:

  • You have removed any and all built-in administrators account from SQL Server logins
  • You have removed all the users (except SA) that were part of SYSADMIN server role (Including any Windows Accounts and/or SQL Server logins)
  • You have set the password of SA to something extremely complex which is hard to remember.
  • For day-to-day operations on SQL Server, you use your domain user account which has DBO permissions on couple of databases but doesn’t have SYSADMIN privileges.

Since you set the SA password to be complex and you have not been using it, you forgot the SA password. You are the only person in the company who would know the SA password and now you have lost the SA password.

What would you do now?

Some quick options I can think of are listed below:

1. You will try to look for the SA password on your computer hard-drive or in your emails (If you stored it in some file which is a bad practice)

2. You will rebuild Master database or reinstall SQL Server and attach all the user databases. However, this could take some time and also doesn’t guarantee that all your logins, users, permissions and server configurations will be recovered unless you plan to restore the Master database from an old backup. However, as you don’t remember the SA password, restoring the Master database will not help you and you are back to square one.

3. You will call up Microsoft PSS

You are now running out of options. What would you do?

There’s a way with which you can gain SYSADMIN access to your SQL Server. However, that would mean your Windows account will need to be a member of the local administrators group.

SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.

Here are the steps you will need to perform:

1. Start the SQL Server instance using single user mode (or minimal configuration which will also put SQL Server in single user mode)

From the command prompt type: SQLServr.Exe –m (or SQLServr.exe –f)

 

Note: If the Binn folder is not in your environmental path, you’ll need to navigate to the Binn folder.

(Usually the Binn folder is located at: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn)

2. Once SQL Server service has been started in single user mode or with minimal configuration, you can now use the SQLCMD command from command prompt to connect to SQL Server and perform the following operations to add yourself back as an Admin on SQL Server instance.

SQLCMD –S <Server_Name\Instance_Name>

You will now be logged in to SQL Server as an Admin.

3. Once you are logged into the SQL Server using SQLCMD, issue the following commands to create a new account or add an existing login to SYSADMIN server role.

To create a new login and add that login to SYSADMIN server role:

1> CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’

2> go

1> SP_ADDSRVROLEMEMBER ‘<Login_Name>’,’SYSADMIN’

2>go

To add an existing login to SYSADMIN server role, execute the following:

1> SP_ADDSRVROLEMEMBER ‘<LOGIN_NAME>’,’SYSADMIN’

The above operation will take care of granting SYSADMIN privileges to an existing login or to a new login.

4. Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need –f or –m)

Note: Those that might be thinking this might make it easy for anyone to get access to SQL Server, well remember that you do have Auditing and will have control of who gets access to the local servers administrators group. If you haven’t enable controls at that level then you may have bigger security issues in hand!!!

Comments (24)

  1. jametock says:

    The single user mode is awful! I also know another way to recover access to sql server:
    https://www.youtube.com/watch?v=GfI-bo5TDB0

    This method works in case you forgot the sa password or sa account got locked out or disabled.

    It’s worth a try.

  2. Anonymous says:

    In sql server 2008 r2 the path has been changed to "C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn"

  3. Shanky_621 says:

    Hello below query is wrong

    CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’

    login name should not be in single quote as per your demo reader will take it in single quote

  4. SQL Lion says:

    Football Team Location Problem | SQL Lion

    T – SQL challenge to solve Football Team Location Problem in a single query.

    http://www.sqllion.com/…/football-team-location-problem

  5. sarah says:

    Excellent – thanks for sharing – please browse for <a href="http://www.sqlservermanagementstudio.net/…/sql-server-2008-management-studio.html"&gt; sql server management studio</a>

  6. Fitsum says:

    Question: Can a user with 'Sysadm' privilege in a SQL Server Instance gain access (be it privileged or any other) to the databases created under that instance without having an active account in that instance?

    An example would be a user authenticated to the SQL server instance through Active Directory Domain group which as been granted a 'sysadm' privilege but the user account does not have an entry in the sysusers table in any of the databases under the same instance.

    Any help would be appreciated.

    Thanks,

    Fitsum.

  7. Jason says:

    You can also use tooling like PSEXEC to launch SQLCMD or even SSMS as the NT AUTHORITYSYSTEM account which by default is a Sysadmin inside SQL Server.  This allows access with the same assumptions, that you have administrative access to the underlying OS.  The nice thing about this approach is that you don't have to stop the service. Argenis blogged about it a while back…    sqlblog.com/…/think-your-windows-administrators-don-t-have-access-to-sql-server-2008-by-default-think-again.aspx

  8. udhayan says:

    "SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges."

    I guess this applies only if the builtinadministrators login is enabled in the SQL Server instance. builtinadministrators is not available in my instance and im not able to login in single user mode,even though im a WINDOWS local administrator

  9. Steve says:

    this doesn’t work in 2014 🙁

  10. Sonika Rawat says:

    Need to reset SQL password of SQL Server admin account then try SQL password recovery software which is a perfect and successful key to unlock SQL account. Read more and try from here: -:
    http://goo.gl/uXNmW7

  11. Himanshu says:

    Worked for me.. thanks

  12. Alex Lam says:

    The SQLServr.Exe –m -S does not work for me. It did not return the command prompt.
    I replaced it with NET START /m"SQLCMD" and then follow your subsequent steps, and it worked.
    Thanks

  13. NathanStevens says:

    SQL database stores data in MDF format which sometimes may get corrupt due to Virus attack, invalid database file header, insufficient available space, corruption of media etc. In such situations the user can use the software SQL Recovery which can recovers
    the data from corrups MDF file. More information about this tool can be found at :-
    http://www.sql.recoverydeletedfiles.com/

  14. Poonam says:

    Now, its not difficult to repair the lost SQL password because there are large amount of password recovery tools are available. Sysinfotools software is a good example where you will find the excellent feature rich SQl password recovery software.

  15. Mark Solomon says:

    Thank you. This worked for me in SQL Serve 2012…

  16. Michael Goebel says:

    Losing password always creates frustrating time for its user and there are lots of reasons which are responsible for password issues. So without wasting any more time I would like to suggest you visit the link:

    http://recoveryandmanagement.com/2014/09/19/recover-sql-database-password/ and know the process of restoring lost or forget the password of SQL server.

  17. Visitor says:

    Does this work even if the builtinadministrators group is removed from sql server (and deprived of SQL server sysadmin as a consequence)?

  18. Software says:

    For recovery of Access File Database to SQl server with this software :-
    http://www.recoverfilesdata.com/access-file-recovery-software.html

  19. WQ says:

    http://www.fashionclothing.mex.com/ Sure,
    http://www.louisvuitton.so/ the process
    http://www.burberryonlineshop.de/ of flinging
    http://www.nikefree-run.org.uk/ a spinnerbait
    http://www.harrods-london.co.uk/ may
    http://www.airmax-90.org/ catch
    http://www.pradashoes.com.co/ a http://www.lululemoncanada.ca/ rogue fish off
    http://www.nike-air-max.com.de/ of
    http://www.yoga-pants.ca/ nothing http://www.rayban.com.de/ here and
    http://www.longchampoutlet.com.co/ there,
    http://www.babyliss.us.org/ but
    http://www.nike-air-max.ca/ I’ve found that
    http://www.nike-roshe-run.de/ during the
    http://www.nike-air-max.com.au/ course
    http://www.the-north-face.ca/ of the http://www.longchamp.us.org/ average true
    http://www.ghdhairstraightener.com.co/ cover-seeking day, your
    http://www.newbalance.com.es/ only looking
    http://www.nike-rosherun.nl/ for a
    http://www.burberry-outlet.me.uk/ few extra bites
    http://www.abercrombie-hollister.nl/ from “between”
    http://www.rolex-watches-canada.ca/ bass.
    http://www.christianlouboutin.org.uk/ I’d
    http://www.swarovski-australia.com.au/ rather
    http://www.hollister-abercrombie.com.se/ be
    http://www.converse.com.de/ locked http://www.tiffany-und-co.de/ and
    http://www.truereligionjeans.net.co/ loaded for
    http://www.bcbgdresses.in.net/ the
    http://www.ralphlaurenoutletonline.in.net/ gentle
    http://www.the-northface.com.co/ pitch, then
    http://www.coachoutlet-online.com.co/ carelessly
    http://www.louis-vuittonoutletcanada.ca/ unleashing
    http://www.longchamp.com.de/ the http://www.mmoncler-outlet.com/ firing
    http://www.montblanc–pens.in.net/ squad.
    http://www.ralphlaurenoutletonline.us.org/ What
    http://www.guccioutlet.us.org/ I
    http://www.uggs.co.nl/ mean
    http://www.cheapoakleysunglasses.ar.com/ is that
    http://www.pandorajewellery.com.au/ in most cases
    http://www.nikeshoesoutlet.org.uk/ I’d
    http://www.handbagsoutlet.com.co/ rather
    http://www.uggbootsclearance.com.co/ be putting along “between”
    http://www.horloges-rolex.nl/ at http://www.toryburchsale.com.co/ a
    http://www.michaelkorsoutlet.ar.com/ moderate pace
    http://www.celinebags.org/ with
    http://www.bcbg-max-azria.ca/ the
    http://www.louisvuitton-outlet.com.co/ same
    http://www.designerhandbagsoutlet.com.co/ jig
    http://www.michaelkors.co.nl/ in my
    http://www.michaeljordan.com.de/ hand that I
    http://www.nike-air-max.com.se/ had
    http://www.thenorthfacejackets.in.net/ success with on
    http://www.burberry–outlet.com.co/ the http://www.prada.com.de/ docks
    http://www.cheapjerseys.us.org/ all
    http://www.maccosmetics.gr.com/ day, and
    http://www.michael-kors.com.co/ looking
    http://www.louboutin.jp.net/ for
    http://www.swarovski-online-shop.de/ that
    http://www.vans-schuhe.com.de/ unique
    http://www.michaelkorsoutletonline-sale.us.com/ cast/pitch
    http://www.cheap-uggboots.in.net/ to
    http://www.mcmhandbags.com.co/ something

  20. Jason says:

    Take a look on SysTools SQL Password Recovery. See here:
    http://www.systoolsgroup.com/sql-password-recovery.html

  21. Cyrus Mak says:

    Thanks, worked perfectly.

  22. aw says:

    http://www.lokerjobindo.com/2015/11/lowongan-kerja-asuransi.html Lowongan Kerja Asuransi
    http://www.lokerjobindo.com/search/label/Lowongan%20Kerja%20Indomart Lowongan Kerja Indomart
    http://www.lokerjobindo.com/2015/11/lowongan-kerja-it-programmer.html Lowongan Kerja IT Programmer
    http://www.lokerjobindo.com/search/label/Lowongan%20Kerja%20Lulusan%20SMK Lowongan Kerja Lulusan SMK
    http://www.lokerjobindo.com/2015/11/lowongan-kerja-pt-pertamina-retail_17.html Lowongan Kerja PT Pertamina Retail
    http://www.lokerjobindo.com/search/label/Lowongan%20Kerja%20Lulusan%20Sarjana Lowongan Kerja Lulusan Sarjana
    http://www.lokerjobindo.com/2015/11/lowongan-kerja-pt-pertamina-retail_17.html Lowongan Kerja PT Pertamina Retail
    http://www.lokerjobindo.com/search/label/Lowongan%20Kerja%20Marketing Lowongan Kerja Marketing
    http://www.lokerjobindo.com/2015/11/info-kerja-professional-insurance.html Info Kerja Professional Insurance
    http://www.lokerjobindo.com/2015/11/lowongan-kerja-d3.html Lowongan Kerja D3
    http://www.lokerjobindo.com/2015/11/lowongan-kerja-pt-bni-life-insurance.html Lowongan Kerja PT BNI Life Insurance
    http://www.lokerjobindo.com/2015/11/lowongan-kerja-pt-surya-madistrindo.html Lowongan Kerja PT Surya Madistrindo
    http://www.lokerjobindo.com/search/label/Lowongan%20Kerja%20Metro%20TV Lowongan Kerja Metro TV
    http://www.lokerjobindo.com/2015/11/loker-terbaru-akunting-pt-sumber.html Loker Terbaru Akunting PT Sumber Alfaria Trijaya, Tbk
    http://www.lokerjobindo.com/2015/11/lowongan-kerja-it-programmer.html Lowongan Kerja IT Programmer
    http://www.lokerjobindo.com/search/label/Lowongan%20Kerja%20Negeri Lowongan Kerja Negeri
    http://www.lokerjobindo.com/2015/11/lowongan-kerja-pt-pertamina-retail_17.html Lowongan Kerja PT Pertamina Retail
    http://www.lokerjobindo.com/search/label/Lowongan%20Kerja%20Pegawai%20Negeri Lowongan Kerja Pegawai Negeri
    http://www.lokerjobindo.com/2015/11/lowongan-kerja-management-trainee.html Lowongan Kerja Management Trainee
    http://www.lokerjobindo.com/search/label/Lowongan%20Kerja%20Penerbangan Lowongan Kerja Penerbangan
    http://www.lokerjobindo.com/2015/11/info-kerja-professional-insurance.html Info Kerja Professional Insurance