A word of caution:
I am a bit apprehensive here, so I would like to make this clear from the beginning: This is really intended for responsible and cautious DBAs who have a legitimate need to access SQL Server but there is no one around with DBA (i.e., sysadmin) role or any access at the instance level for that matter. Yes, there can still be loopholes in your SQL Servers that, if left open, can be leveraged by "ethical" as well as rogue actors alike. Here is a relatively old article discussing some of the ways SQL Server security might be compromised:
So, always stay vigilant!
How do you manage and troubleshoot a SQL Server if you don't any access to it at all? You can't. Even if you have full administrative rights on the server or even in the entire IT network, you cannot connect to the SQL Server if you don't have valid credentials with sysadmin rights.
Unless...... it is a very very old SQL Server version, like SQL Server 7 or older. Back then SQL Server would automatically add the local administrators (BUILTIN\Administrators) as sysdba during the installation. You could go back and remove that access, but I don't think most people bothered. But back then there was even a bigger security issue with SQL Server, blank password for the almighty sa user by default!!!!!
Fortunately those dark days are long gone, SQL Server has come a very long way since then and so have we, in our mindsets regarding the security.
But that is not what I am here to talk about.
So back to the topic, this problem per se is not new and neither is the solution. Most long time DBAs would know the solution already, as they would have run into this issue at some point in their career.
Let me just describe the solution briefly and get it out of the way as this article is not about that.
The solution requires that you are a member of the local administrators group on the computer where SQL Server instance is installed. You log into the server, stop and then restart the SQL Server in a single user mode, that temporarily grants local administrators sysadmin permissions. You then connect to the SQL instance with Windows Authentication, and make yourself or any login you like a member of the sysadmin role. Then stop and restart the SQL service in normal mode. VOILÀ!
Below is a link to a how-to document on this very topic from Microsoft:
Recently, I got a new gig and had to solve this problem on more than 200 SQL Servers, over a single weekend! Given the short amount of time and a relatively large number of SQL Servers, I decided to write a PowerShell script that I can then call remotely on each SQL Server. At first the PowerShell code was a relatively short one, without any validation or error checking. It basically restarts the SQL instance in single user mode, adds a AD domain group for DBAs to the sysadmin role then restarts back the SQL Instance and the SQL Agent services:
# INPUTS $sql_instance_name = 'MSSQLSERVER' $login_to_be_granted_access = 'Contaso\Group-MSSQL-DBAs' # GET THE NAME FOR THE WINDOWS SERVICE AND THE SQL CONNECTION if($sql_instance_name -eq "MSSQLSERVER") { $service_name = 'MSSQLSERVER' $sql_server_instance = "." } else { $service_name = "MSSQL`$$sql_instance_name" $sql_server_instance = ".\$sql_instance_name" } $sql = "CREATE LOGIN [$login_to_be_granted_access] FROM WINDOWS GO ALTER SERVER ROLE sysadmin ADD MEMBER [$login_to_be_granted_access] GO " $check_permission = "IF EXISTS (SELECT * FROM sys.server_role_members WHERE member_principal_id = SUSER_ID('$login_to_be_granted_access') AND role_principal_id = SUSER_ID('sysadmin')) PRINT 'VERIFIED' ELSE RAISERROR('ERROR: Verification failed.', 16, 1) GO " # STOP/START SQL SERVER IN SINGLE USER MODE Stop-Service -Name $service_name -Force net start $service_name /f /m"SQLCMD" Start-Sleep 2 sqlcmd.exe -E -S $sql_server_instance -Q $sql sqlcmd.exe -E -S $sql_server_instance -Q $check_permission # Stop the service Stop-Service -Name $service_name -Force # RESTART SQL SERVICES IN NORMAL MODE $service = Start-Service -Name $service_name -PassThru Start-Service -Name $service.DependentServices[0].Name
Note: Restarting a Windows service requires administrator privileges, including for the process from where the service is being restarted from, which in this case is PowerShell and therefore it needs to be launched as “Run As Administrator”:
Later, I added more code for validation, error checking, and an option to create a SQL Login instead of a Windows Login. Most importantly, I added the ability to run this script remotely. As a result, the script is now longer than it perhaps should be.
##### CAUTION: THE SCRIPT WILL STOP AND RESTART YOUR SQL SERVER INSTANCE!!!!!!!!! <# THIS SCRIPT IS INTENDED TO GET ACCESS TO SQL SERVER ONLY IF YOU DON'T HAVE SYSADMIN PERMISSION. USE ONLY IN EMERGENCY. #> <# .NOTATION This script is intentionally lengthy for several important reasons: - It must handle multiple complex steps safely to grant sysadmin access to SQL Server - It needs to validate environment prerequisites such as elevated rights and service state - The script carefully stops, starts, and manages dependencies of SQL Server services - Confirmation prompts and detailed error handling are included to prevent unintended disruptions - To avoid automation mistakes, explicit validations and user prompts are mandatory - The overall complexity reflects the sensitive operation of forcibly gaining sysadmin access Please read and understand each section of the script carefully before using it, and always run this script in a controlled environment with proper permissions. #> <# REQUIREMENTS: 1. Local Administrator rights on the server 2. Run locally or via Invoke-Command with PSRemoting enabled (default on Windows Server 2012+) 3. Elevated PowerShell session (Run as Administrator) PARAMETERS: - $login_to_be_granted_access (string, required): Windows or SQL login to grant sysadmin access - $sql_instance_name (string, optional): SQL instance name (default instance if omitted) - $confirm (bool, optional): Prompt for confirmation before stopping SQL service. Default: $true - $sql_login_password (string, optional): SQL login password required if SQL login #> <# Examples: Example 1: Running the Script Locally - Save the script on your local device, for example as C:\Scripts\Gain-SqlSysadminAccess.ps1 - Open PowerShell with Administrative privileges (Run as Administrator). - Navigate to the folder containing your script, for example: cd C:\Scripts - Run the script with required parameters. For example, to grant sysadmin access to a Windows login named "DOMAIN\User1" on default instance with confirmation prompt: .\Gain-SqlSysadminAccess.ps1 -login_to_be_granted_access "DOMAIN\User1" -confirm $false Example 2: Running the Script Remotely Using Invoke-Command - From your local machine with PowerShell launched as Administrator, run the script on a remote computer (e.g., RemoteServer01). Make sure PSRemoting is enabled on the remote server. - Use the following command to invoke the script remotely: # Run the local script on a remote computer, passing parameters Invoke-Command -ComputerName RemoteServer01 ` -FilePath "C:\Scripts\Gain-SqlSysadminAccess.ps1" ` -ArgumentList "DOMAIN\User1", "SQL2022AG01", $false - Replace parameters accordingly for your target environment #> param ( [string] $login_to_be_granted_access = 'sqladmin', [string] $sql_instance_name = 'SQL2022AG01', [bool] $confirm = $true, [string] $sql_login_password = 'WA1!!1P7JRjN7F4eibEES&IxU%Elgw6b#' ) # Set default preferences $ErrorActionPreference = 'Stop' $WarningPreference = 'Continue' $InformationPreference = 'Continue' # Assume default instance if sql_instance_name not specified if (-not $sql_instance_name) { $sql_instance_name = 'MSSQLSERVER' } if ($null -eq $confirm) { $confirm = $true } Write-Information "Computer Name: $env:COMPUTERNAME" Write-Information "SQL Instance Name: $sql_instance_name`n" # Confirm prompt if required if ($confirm) { $valid_responses = 'Yes', 'yes', 'No', 'no' do { Write-Warning "##### CAUTION: THE SCRIPT WILL STOP AND RESTART YOUR SQL SERVER INSTANCE!!!!!!!!!" $response = Read-Host "Are you sure you want to continue (Yes/No)?" if (-not $valid_responses.Contains($response)) { Write-Host "Please enter Yes or No" } } until ($valid_responses.Contains($response)) if ($response -in @('No', 'no')) { return } } else { Write-Warning "Confirmation prompts are disabled." Write-Information "" } # Validate mandatory parameters if (-not $sql_instance_name -or -not $login_to_be_granted_access) { throw "Error: Both `\$sql_instance_name` and `\$login_to_be_granted_access` are required." } if (-not $login_to_be_granted_access.Contains('\') -and -not $sql_login_password) { throw "A password must be provided for SQL Login." } # Check for elevated privileges $isAdmin = ([System.Security.Principal.WindowsIdentity]::GetCurrent()).Owner -eq 'S-1-5-32-544' if (-not $isAdmin) { throw "Error: Powershell must be launched in elevated privileges mode (Run as Administrator)." } # Determine service and SQL Server instance names if ($sql_instance_name -eq 'MSSQLSERVER') { $service_name = 'MSSQLSERVER' $sql_server_instance = '.' } else { $service_name = "MSSQL`$$sql_instance_name" $sql_server_instance = ".\$sql_instance_name" } Write-Information "SQL Server Instance: $sql_server_instance" Write-Information "Service Name: $service_name`n" # Get SQL service and dependent services $sql_service = Get-Service -Name $service_name -ErrorAction Stop $dependent_services = $sql_service.DependentServices if (-not $sql_service) { throw "Error: SQL instance '$sql_instance_name' or service '$service_name' not found." } Write-Information "Service Status: $($sql_service.Status)" Write-Information "Service Startup Type: $($sql_service.StartType)`n" # Re-enable if disabled if ($sql_service.StartType -eq 'Disabled') { Write-Warning "SQL instance '$sql_instance_name' is currently disabled." if ($confirm) { Set-Service -Name $service_name -StartupType Manual -Confirm } else { Set-Service -Name $service_name -StartupType Manual } $sql_service.Refresh() if ($sql_service.StartType -eq 'Disabled') { throw "Error: Cannot continue while SQL instance is Disabled." } } # Stop the service if running if ($sql_service.Status -eq 'Running') { Write-Warning "Stopping service: $service_name and its dependent services..." if ($confirm) { Stop-Service -InputObject $sql_service -Confirm -Force } else { Stop-Service -InputObject $sql_service -Force } Start-Sleep -Seconds 1 $sql_service.Refresh() if ($sql_service.Status -ne 'Stopped') { throw "Error: SQL instance service '$service_name' did not stop as expected." } } # Start the service in single-user mode if appropriate $sql_service.Refresh() if ($sql_service.Status -ne 'Running' -and $sql_service.StartType -in @('Manual', 'Automatic')) { Write-Warning "Starting SQL Server service in single user mode..." Write-Information "" net start $service_name /f /m"SQLCMD" | Out-Null Start-Sleep -Seconds 1 $sql_service.Refresh() if ($sql_service.Status -eq 'Running') { if ($login_to_be_granted_access.Contains('\')) { $sql = @" CREATE LOGIN [$login_to_be_granted_access] FROM WINDOWS; GO ALTER SERVER ROLE sysadmin ADD MEMBER [$login_to_be_granted_access]; GO SELECT @@ERROR AS [ErrMsg]; GO "@ } else { $sql = @" CREATE LOGIN [$login_to_be_granted_access] WITH PASSWORD=N'$sql_login_password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; ALTER SERVER ROLE sysadmin ADD MEMBER [$login_to_be_granted_access]; SELECT @@ERROR AS [ErrMsg]; "@ } Write-Information "Adding login '$login_to_be_granted_access' to SYSADMIN role..." Write-Information $sql sqlcmd.exe -E -S $sql_server_instance -Q $sql Write-Information "" $check_permission = @" IF EXISTS ( SELECT * FROM sys.server_role_members WHERE member_principal_id = SUSER_ID('$login_to_be_granted_access') AND role_principal_id = SUSER_ID('sysadmin') ) PRINT '****** VERIFICATION SUCCEEDED ****************' ELSE RAISERROR('ERROR: Verification failed.', 16, 1); GO "@ Write-Information "Verifying sysadmin permissions..." Write-Information $check_permission sqlcmd.exe -E -S $sql_server_instance -Q $check_permission Write-Information "" Write-Information "Restarting SQL instance in normal mode..." net stop $service_name | Out-Null net start $service_name | Out-Null Write-Information "" Write-Information "Restart dependent services if they were running previously" Write-Information "" $dependent_services | Format-Table -Property DisplayName, Status, StartType Write-Information "" foreach ($dependent_service in $dependent_services) { $dependent_service_name = $dependent_service.Name if ($dependent_service.Status -eq 'Running') { if ((Get-Service -Name $dependent_service_name).Status -ne 'Running') { Write-Information "Starting dependent service: $dependent_service_name" $dependent_service.Start() } } } } else { throw "Error: SQL instance did not start as expected." } }
Now what? How do you use it then? If it is just one or two SQL Servers, you can simply copy/paste it into an elevated PowerShell session, and edit the param block with the appropriate values.
param ( [string] $login_to_be_granted_access = 'sqladmin', [string] $sql_instance_name = 'SQL2022AG01', [Boolean] $confirm = $true, [string] $sql_login_password = 'WA1!!1P7JRjN7F4eibEES&IxU%Elgw6b#' )
Note: You can give a Windows login for the $login_to_be_granted_access parameter, as long as it contains a "\" in it, the script will know it is a Windows login.
Then click the green Run Script button or hit F5 key:
It will display a warning and ask if you want to continue:
It will also ask to confirm again before stopping the SQL Service.
You can disable the confirmation prompts by setting $confirm = $false, which is what I would do when running this in non-interactive mode, especially in a batch mode when running it against multiple SQL Servers at the same time. In the following example, the script is saved in a file "Gain-SqlSysadminAccess.ps1, then using the Invoke-Command, I can run it on a remote server (highlighted):
# Add integrated/windows authenticated login or group $script_file_path = "Gain-SqlSysadminAccess.ps1" Invoke-Command -ComputerName SQLServerVM01 -FilePath $script_file_path ` -ArgumentList 'Contaso\Group-MSSQL-DBAs', 'SQL2022AG01', $false
# Add a SQL Server authenticated login $script_file_path = "Gain-SqlSysadminAccess.ps1" Invoke-Command -ComputerName SQLServerVM01 -FilePath $script_file_path ` -ArgumentList 'sqladmin', 'SQL2022AG01', $false, 'WA1!!1P7JRjN7F4eibEES'
To run the script on multiple SQL Servers, you can save the list of SQL servers, instance names and login name in a CSV file. For integrated/Windows logins, the LoginName needs to be in "ServerName\UserName" or "Domain\UserName" format and must contain a "\" as that is what my script is using to dynamically determine whether is is Windows login or a SQL login. For example:
Then use the following PowerShell code to execute the script against all servers listed in the CSV file:
$script_file_path = "Gain-SqlSysadminAccess.ps1" $csv_data = Import-Csv -Path 'C:\Users\dummy\Documents\myServers.csv' foreach($csv_row in $csv_data) { Invoke-Command -ComputerName $csv_row.ServerName -FilePath $script_file_path ` -ArgumentList $csv_row.LoginName, $csv_row.SQLInstance, ` $false, $csv_row.Password }
I hope you find this useful in times of need. If you see or come across a bug in this PowerShell code, please let me know.