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;
- Stop SQL Server service using SQL Server configuration Manager.
- Right click on SQL Server service and get properties.
- 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.
- 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.
- Click on Apply and then Ok.
- Start SQL Server service.
- 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.
- sqlcmd -E -S <server name> -q "select @@servername"
- Use the below sqlcmd statement to create a new user called “recovery”.
- CREATE LOGIN recovery WITH PASSWORD = '1qaz2wsx@'
- Use the below sqlcmd statement to grant admin privilege to the user we just created.
- SP_ADDSRVROLEMEMBER 'recovery',SYSADMIN
- Stop the SQL Server service using Configuration Manager.
- Get SQL Server service properties and remove the startup parameter “-m” and click Ok.
- Start the SQL Server service.
- Open SSMS and try to connect to the server using the user name and password we created in above steps.
Hope this helps. Cheers!