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

0

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.

Vivek Garg

  1. gabe January 12, 2015 at 5:46 pm

    Does this work if the Local Administrators group login is not a SQL SERVER user , has been disabled or is not an SA?

    • Vivek January 13, 2015 at 1:30 pm

      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.

%d bloggers like this: