How do I get access to SQL Server instance when no other access is possible

Recently I had a situation where no one knows any level of credentials to SQL a Server instance. The instance I tried was a SQL Server 2008. By default SQL Server 2008 does not provide admin access to built in Windows local admin group.

I was able to get access to it by following below steps;

1. Stop SQL Server service using SQL Server configuration Manager.
2. Right click on SQL Server service and get properties.
3. In Log On tab, use This account option to provide a windows domain account credentials which you already know. I my case I provided my credentials for the specific domain.
4. Use Advanced tab to add “-m;” startup parameter to the beginning of the parameter list. The –m startup parameter is use to start SQL Server service in single user mode.
5. Click on Apply and then Ok.
6. Start SQL Server service.
7. Open CMD shell in administrator mode and type the following sqlcmd statement; This will test the access to the server and if it successful, it returns the SQL Server name. This is just a verification.
1. sqlcmd -E -S <server name> -q "select @@servername"
8. Use the below sqlcmd statement to create a new user called “recovery”.
1. CREATE LOGIN recovery WITH PASSWORD = '1qaz2wsx@'
9. Use the below sqlcmd statement to grant admin privilege to the user we just created.
1. SP_ADDSRVROLEMEMBER 'recovery',SYSADMIN
10. Stop the SQL Server service using Configuration Manager.
11. Get SQL Server service properties and remove the startup parameter “-m” and click Ok.
12. Start the SQL Server service.
13. Open SSMS and try to connect to the server using the user name and password we created in above steps.

Hope this helps. Cheers!

Reference:

http://blogs.technet.com/b/sqlman/archive/2011/06/14/tips-amp-tricks-you-have-lost-access-to-sql-server-now-what.aspx