How do I manage the instance without access? Thankfully by putting the instance in Single User mode I am able to add my windows account to the instance and add it to the SysAdmin role.
** Note this will require taking the Database offline **
- Open the Windows Services admin tool (services.msc)
- Open the properties of the SQL instance in question, select the path to execute and copy the full line (i.e. "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE2008R2\MSSQL\Binn\sqlservr.exe" -sINSTANCENAME)
- Stop the MSSQL service.
- Open a Command Prompt using Run As Administrator. (if you don't select Run As Administrator the service won't start)
- Paste the path copied above and add a "-m" to the end. This will start SQL Server in single-user mode.
- Execute the command and you should see the SQL instance start up. Leave this window open.
- Open a new command prompt using Run As Administrator. (if you don't select Run As Administrator you will have problems connecting)
- run “SQLCMD –S Server_Name\Instance_Name -E” (the switch command are case sensitive)
- In this window, run the following lines, pressing Enter after each one:
1> CREATE LOGIN [domainName\loginName] FROM WINDOWS
2> GO
3> SP_ADDSRVROLEMEMBER 'LOGIN_NAME','SYSADMIN'
4> GO - Use Ctrl+C in both windows to close exit the SQLCMD and Stop SQL in Single User Mode
- Start the SQL Instance in Services admin tool.