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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s