PROBLEM
DBAs need to have sysadmin access on SQL Server to support Databases effectively. There may come situations where DBAs neither have sysadmin access nor [sa] password. In that case, DBA needs to perform below activity to get the access.
RESOLUTION
PREREQUISITES:
1) Administrative privileges needed on Host Server.
2) Downtime of approximately 10 minutes needed because SQL Server would be started in ‘Single User’ mode.
PROCEDURE:
1. RDP to the server with the account which has ‘Admin’ rights on the Hosting Windows box. Using a service account is good idea.
2. Open ‘SQL Server Configuration Manager’ :
Start>All Programs>Microsoft SQL Server 2008 (or 2005)>Configuration Tools>SQL Server Configuration
Manager
3. Start SQL Services in ‘Single User Mode:
- Right Click on SQL Services and click ‘properties’
- Go ‘Advanced’ tab then open ‘Start up Parameters’
- Write ‘-m’ at the end, then click ‘OK’.
This would open a dialog box informing that SQL Services need to be restarted for this. Click ‘OK’.
- Stop dependent services like SQL Agent. Restart SQL Services.
4. Connect to SQL instance using SQLCMD
- Right Click on Command prompt, select ‘Run as Administrator’ (in Windows 2003 and lower, run as administrator is not available and not required)
- Now connect to SQL instance using SQLCMD, write below command:
- SQLCMD –E –S <instance name>
5. Now create a windows login (i.e. group account used by DB team), use below command:
CREATE LOGIN [Login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
6. Now grant ‘sysadmin’ access to this newly created login, use below command:
EXEC master..sp_addsrvrolemember @loginame = 'login_name', @rolename = 'sysadmin'
GO
7. Restart SQL Services in multi user mode. For doing that, remove ‘-m’ from Startup parameters and restart SQL
Services.
Now DBA gets sysadmin access at SQL Server with the above created Login Name.
2 responses to “How to get access on SQL Server if you don’t have “sa” password or sysadmin access?”
Does this work if the Local Administrators group login is not a SQL SERVER user , has been disabled or is not an SA?
Hello Gabe,
Yes it will work even if the Administrator group is not added at SQL server or it has been disabled. I have tested this with SQL server 2012. You can test and let me know whether u get success or not.