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.
