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.
1) Administrative privileges needed on Host Server.
2) Downtime of approximately 10 minutes needed because SQL Server would be started in ‘Single User’ mode.
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
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]
6. Now grant ‘sysadmin’ access to this newly created login, use below command:
EXEC master..sp_addsrvrolemember @loginame = 'login_name', @rolename = 'sysadmin'
7. Restart SQL Services in multi user mode. For doing that, remove ‘-m’ from Startup parameters and restart SQL
Now DBA gets sysadmin access at SQL Server with the above created Login Name.