Forcing Access to SQL Server

I recently inherited a few SQL instances that I did not have access to. You can use one of the following methods to add a login and give yourself access.

  • Option 1 (Using PSExec/No Server Restart Required)
    • Download PSExec from Microsoft
    • Remote desktop into the server hosting the instance.
    • Use PsExec to launch SQL Server Management Studio
      • PsExec -s -i “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe” (Update your path based on your setup.)
    • Add a SQL login
    • If you run into errors you may need to disable UAC.
  • Option 2 (Requires SQL Server Restart)
    • Remote desktop into the server hosting the instance.
    • Stop the SQL server service.
    • Open an elevated command prompt. Browse to the SQL Binn folder.
    • Run “sqlservr -m” to start the server in single user mode.
    • Launch another elevated command prompt.Launch sqlcmd (sqlcmd -E)
    • Run commands to add login and add to sysadmin
      • Create login TestUser with password=’Test’
      • Exec sp_addsrvrolemember [TestUser], [sysadmin]
    • Restart the SQL server service.