Getting Sysadmin Access to SQL Server When Locked Out

By:   |   Comments (7)   |   Related: > Security


We all have probably seen this. As a SQL Server DBA, you’re expected to support anything resembling a SQL Server in your environment regardless of who installed it, or whether you have or can get sysadmin access to it.  

It happens with vendor installed instances of SQL Express, many times installed without your knowledge and stuffed on the C:\ drive of a workstation and left unpatched. I see this happen typically with keycard door systems, shipper application systems and manufacturing applications.

Is there anything you can do if SQL Authentication is enabled, but no one knows the sa password or if no one else has sysadmin access? Fortunately, the answer is, yes, there are ways.

Your first thought is probably to restart the SQL Server in single user mode, add a login and put it in the sysadmin group and you’re done. Great idea, but what if you can’t or just don’t want to disrupt a system? It may be that the system is accessed 24x7 in a manufacturing environment. Mission critical applications that run on workstation class machines using SQL Server Express Edition do exist. And sometimes you just don’t want to disrupt a system that you will now own just because you touched it once and now everything that breaks in the future gets blamed on you. This happens.


Good news, there is another way. This link will take you to a great blog post by Jason Brimhall that details how to set up a Windows Task Scheduler Job that runs as ‘NT AUTHORITY\SYSTEM’ and executes a short SQL script to add a login without disrupting anything at all. All you need is Windows admin credentials on the machine hosting the SQL Server you’re trying to access and to follow the steps.

I’ve run this successfully on a Windows 10 machine running SQL Server 2016 Developer Edition and on Windows 7 machines running SQL Server 2012 Express Edition. You could stop right here, ignore the rest of this tip and have a fantastic new tool in your toolbox, but I am hoping you will continue reading. I had a few systems I wanted to do this on and simply applying the same steps would have been more than sufficient. But sometimes I’m obsessed with making things even more easily repeatable than they are already, so I made some minor tweaks to the Task Scheduler job and saved it to an .xml file we can configure and import it in the Task Scheduler and simply run the job.

Good, you decided to read on. First, copy and paste the following XML into your .xml editor of choice, edit SERVERNAME and DOMAIN\user in both places to reflect the SQL Server you’re adding the login to and name of the AD domain account you’re adding in the highlighted places on the sqlcmd.exe command line. Next, save it as an .xml file (I chose C:\temp\AddSysadmin.xml for this example) on the Windows server you’re working with. 

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="">
<Triggers /> 
<Principal id="Author">
<Actions Context="Author">
<Arguments>-E -S
[DOMAIN\user] FROM WINDOWS EXEC sp_addsrvrolemember 'DOMAIN\user','sysadmin'"</Arguments>

Now, let’s open our Task Scheduler using the keyboard shortcut [Windows]+[R] > enter taskschd.msc > OK to open the Windows Task Scheduler.

launch task scheduler

Next, we import the .xml file into it to create a Job.

Action > Import Task…

task scheduler

Select C:\temp\AddSysadmin.xml > Open

task scheduler


Right click on job and choose Run

task scheduler

Give it a couple of seconds and if it still says “Running” hit F5 to refresh and it should go back to “Ready”.

task scheduler

Now let’s confirm the login was added and it’s in the sysadmin server group by connecting to the SQL Server with Management Studio with your newly added login.

login screen

If you’re able to connect the login has been added. 

Expand Logins and find the account you added

Expand Logins, right click and select Properties.

login properties

In Server Roles, you should see you’re in the sysadmin group.

login properties

If it’s successful we can clean up by going back to the Task Manager, right click on the job and choose Delete.

task scheduler

Delete C:\temp\AddSysadmin.xml to complete the cleanup.


Here are a couple of things I ran into with my testing.

The Last Run Result for the Task Scheduler Job should be “The operation completed successfully. (0x0)”. If you get anything other than this message the likely culprit is a mistake with your SERVERNAME variable. Double check and correct if necessary, including making sure the named instance is correct if you’re using a named instance. Probably a good idea to double check DOMAIN\user in both places as well.

Another issue I encountered that was a bit trickier was due to a change in the Local Security Policy that was applied during testing, but unknown to me at the time. After executing the steps above and verifying the Task Scheduler Job result was successful the login was still not created. Adding an output file switch to sqlcmd.exe with a -o captured the error:

“Msg 15247, Level 16, State 1 User does not have permission to perform this action”. 

The next step was to attempt running it as a user that was known to be in the sysadmin group as a test. That attempt produced a rather cryptic message:

“Task Scheduler cannot apply your changes. The user account is unknown, the password is incorrect, or the user account does not have permission to modify the task.” 

task scheduler message

I found this that made sense of it all:

I found out “Network access: Do not allow storage of passwords and credentials for network authentication” was enabled on the machine I was testing with. Disabling it allowed everything to work. Note: this is helpful regardless if you’re manually creating the Task Scheduler Job or importing it from an .xml file.

local security policy
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Joe Gavin Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, August 12, 2021 - 3:01:50 PM - Jeff Back To Top (89123)
This works fantastic, saved me a lot of back and forth with a reluctant sys admin who has a little SQL knowledge and a lot of control issues...

Wednesday, August 22, 2018 - 1:08:04 PM - Joe Gavin Back To Top (77272)

Volker, you would still have to recycle the SQL Server with reset-dbadmin.

Wednesday, August 22, 2018 - 7:06:02 AM - Joe Gavin Back To Top (77268)

I'm happy to see this leading to even more solutions. 

Tuesday, August 21, 2018 - 10:00:31 AM - Volker Bachmann Back To Top (77263)

Maybe this is another solution?

Tuesday, August 21, 2018 - 9:58:19 AM - Bob Feldsien Back To Top (77262)

Fascinating article.  Frankly I'm not sure I want my developers knowing how to do this so I kind of hope they don't read it.

Tuesday, August 21, 2018 - 7:53:13 AM - Don Kolenda Back To Top (77261)

 This is a great post, Joe.  Thanks for sharing!

Tuesday, August 21, 2018 - 3:34:45 AM - Kartar Rana Back To Top (77260)

There is a similar post. This has been tweaked a bit:-

get free sql tips
agree to terms