Regain sysadmin rights to a SQL instance without stopping its service

I came across a situation a few weeks ago where there was a set of SQL servers, some with single and others with multiple instances, which were missing the required domain groups for management.

Worse, I had no permissions to the server, and without these, I was unable to login to the SQL instance. Another issue was that under no circumstances could any of the instances be stopped.

After a bit of head bashing I remembered that by default installation, the local system admin account is a system administrator in SQL Server, and it’s not one I remove after installation.

If I could write a script to add the required groups in, and run a script in a local system context then maybe i could restore the required permissions.

I came up with the script below. The only caveat is that Local System must be resident within the instance level users, and have sysadmin rights.

It’s a bit of a hack, but it served its purpose. 🙂

$task = @'
<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="">
    <Principal id="Author">
  <Actions Context="Author">
      <Arguments>-file d:\addSysadmin.ps1</Arguments>

$code = @'
function Invoke-SQLQuery

        [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, HelpMessage = 'Query')] [string[]] $Query,
        [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true, HelpMessage = 'Database')] [string] $Database = 'master',
        [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, HelpMessage = 'Instance')] [string] $SQLServer
            $ErrorAction = 'Stop' 
            $SqlConnection = New-Object  -TypeName System.Data.SqlClient.SqlConnection
            $SqlConnection.ConnectionString = "Server=$($SQLServer);Database=`'$($Database)`';Integrated Security=SSPI;"
            ForEach ($qry in $Query) {
            $SqlCmd = New-Object  -TypeName System.Data.SqlClient.SqlCommand
            $SqlCmd.CommandText = $qry
            $sqlcmd.Connection = $SqlConnection
            $SqlAdapter = New-Object  -TypeName System.Data.SqlClient.SqlDataAdapter
            $SqlAdapter.SelectCommand = $SqlCmd
            $DataSet = New-Object  -TypeName System.Data.DataSet
            Try {
            $nSet = $SqlAdapter.Fill($DataSet)
            Catch {
            $Tables = $DataSet.Tables

$query = @"
EXEC master..sp_addsrvrolemember @loginame = N'mydomain\mygroup', @rolename = N'sysadmin'

$instances = Get-Service | Where {($_.Name -like 'mssql$*')} | Select -ExpandProperty Name | ForEach {$_ -replace 'MSSQL\$',"$env:computername`\"}
ForEach ($instance in $instances) {
Invoke-SQLQuery -Query $query -SQLServer $instance

$instances = Get-Service | Where  {($_.Name -eq 'MSSQLSERVER')} | % {"$env:computername"}
ForEach ($instance in $instances) {
Invoke-SQLQuery -Query $query -SQLServer $instance
$ErrorActionPreference = 'Stop'
    Remove-Item -Path d:\addSysadmin.ps1 -Force
    Remove-Item -Path d:\task.xml -Force


$code | Out-File -FilePath d:\addSysadmin.ps1 -Force
$task | Out-File -FilePath d:\task.xml -Force
schtasks.exe /Create /XML d:\task.xml /TN addperms
schtasks.exe /Run /TN 'addperms'

$taskStatus = schtasks.exe /query /tn addperms
While ($taskStatus[4] -like '*runn*')
    Start-Sleep -Milliseconds 100
    $taskStatus = schtasks.exe /query /tn addperms
schtasks.exe /delete /tn addperms /f
Remove-Item -Path d:\addSysadmin.ps1 -Force
Remove-Item -Path d:\task.xml -Force

In the script, two text blocks are created.

The first one, assigned to the $task variable, is the XML for the scheduled task we are going to create.

The second one, assigned to $code, is the actual powershell code that the scheduled task would run. This code contains a function for executing the required sql commands, and additionally there is code which iterates through each instance on the server and runs the function with the required SQL commands to add the required permissions to each instance. The script writes these text blocks out to new files (in my case on the D drive).

Then, schtasks.exe is used to create the scheduled task, using the new XML file we’ve just created as the input source. Amongst other settings, the XML specifies that the tasks runs in local system context, and also the command and arguments to be executed. The arguments point to the second file just created, the .ps1 file. schtasks.exe is then used again to trigger the job and we wait until the status of the job changes from running to anything else. Once the task has completed, or failed, it is removed from Task Scheduler. Finally, the script removes the files it has just created so no footprint is left on the system at all. Sounds a bit ‘naughtyish’, but it’s really just to keep things tidy!

The code can be run remotely by specifying it as a scriptblock in an Invoke-Command against a remote system, if so desired.

NB You should also be able to use this for also adding single domain accounts, as the formatting of the sql command will be exactly the same. Just replace or add the required details.

You can find the source code for this at my repo