DBA THINGS

How to get access on SQL Server if you don’t have “sa” password or sysadmin access?

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.

Related articles across the web

Sachin Diwakar

,
, , , , ,

2 responses to “How to get access on SQL Server if you don’t have “sa” password or sysadmin access?”

  1. 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.