Recently I was asked to assist someone with access to a Microsoft SQL 2008 R2 Server. Under normal conditions I would simple add them, but this isn’t the normal. I do not have access I need, so I will add myself so that I can add them.
Issue:
No one seems to have admin access to the SQL
The built-in sa account was not available (windows authentication mode)
Solution:
re-install SQL Server
Better Solution:
Without the need to go into the basics I am going to assume you know your way around SQL Server and the server its hosted on.
Using the SQL Configuration Manager
1. Stop the instance of SQL
2. Stop all over SQL related services that might attempt to connect
3. Open a command prompt as administrator, and find your way to the SQL folder
( C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe” )
4. I then start SQL Server via command line with the -m parameter. This runs SQL Server in single-user mode. Do not close the command prompt.
Using SQL Server Management Studio.
1. Now that you are in single user more use the SQL Server Management Studio (SSMS).
2. Next we add the local account or domain account you need. Remember to assigned them sysadmin roles.
3. When completed you can return to the previous command prompt and [CTRL] C and you will be asked if you would like to shutdown SQL. Type “Y” for yes. When SQL is offline, start SQL using the services console or SQL Configuration Manager.
It’s like magic isn’t it. I hope this helps.
–updated notes —
I ran into this issue also with SQL Express and while the same steps above apply we needed to also define the instance of SQL that was running. Here are the steps:
1. Shut down SQL Server from services
2. Open the command prompts (as admin) and run single-user mode as local admin with this command – “C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe” -m -s SQLEXPRESS
3. Go to the steps I provided under “Using SQL Server Management Studio”