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('NET\Role-MSSQL-DBAs') 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 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 and error checking and an option to create a SQL Login instead of a Windows Login. So, now the script is longer than maybe it 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. SO ONLY USE THIS IN EMERGENCY ON THE PLUS SIDE, IT HAS OPTION TO PROMPT YOU FOR CONFIRMATION BEFORE STOPPING THE SQL INSTANCE REQUIREMENTS:. 1. YOU MUST HAVE THE LOCAL ADMINISTRATOR RIGHTS ON THE SERVER 2. YOU ARE RUNNING THE SCRIPT LOCALLY ON THE SERVER, OR THROUGH THE INVOKE-COMMMAND CMDLET AND THE PSREMOTING MUST BE ENABLED ON THE TARGET SERVER, WHICH IT SHOULD BE BY DEFAULT BE ON WINDOWS SERVER 2012 AND UP 3. THE POWERSHELL MUST BE LAUNCHED WITH ELEVATED ADMINISTRATIVE PRIVILEGES PARAMETERS: ONLY THE FIRST PARAMETER, $login_to_be_granted_access, IS REQUIRED 1. $login_to_be_granted_access --> This can be a Windows Login or SQL Login $sql_login_password is required for SQL Login 2. $sql_instance_name -----------> Only specify the sql instance name without the server name If omitted, the default instance is assumed 3. $confirm ---------------------> $true or $false to prompt for confirmation Optional: Only required for SQL Login 4. $sql_login_password ----------> Password for the SQL Login #> param ( [string] $login_to_be_granted_access = 'sqladmin', [string] $sql_instance_name = 'SQL2022AG01', [Boolean] $confirm = $true, [string] $sql_login_password = 'WA1!!1P7JRjN7F4eibEES&IxU%Elgw6b#' ) # set the default preferences $ErrorActionPreference = 'Stop' $WarningPreference = 'Continue' $InformationPreference = 'Continue' # if value for sql_instance_name is blnak then assume the default instance if (-Not ($sql_instance_name)) {$sql_instance_name = 'MSSQLSERVER'} # if value for $confirm is blank then assume $true if ($confirm -eq $null) {$confirm = $true} $msg = 'Computer Name: ' + $env:COMPUTERNAME Write-Information $msg $msg = 'SQL Instance Name: ' + $sql_instance_name Write-Information $msg Write-Information "" # SHOW PROMPT IF $confirm IS TRUE if ($confirm -eq $true) { $valid_responses = "Yes", "yes", "No", "no" do { Write-Warning "##### CAUTION: THE SCRIPT WILL STOP AND RESTART YOUR SQL SERVER INSTANCE!!!!!!!!!" $response = Read-Host -Prompt "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 '$confirm is false therefore all prompts will be suppressed.' Write-Information "" } # LETS DO A BIT OF VALIDATION if (-not ($sql_instance_name) -or (-not ($login_to_be_granted_access))) { Throw 'Error: Values for $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 given for SQL Login' } # CHECK IF RUNNING POWERSHELL IN ELEVATED PRIVILEDGES MODE if(-Not (([System.Security.Principal.WindowsIdentity]::GetCurrent()).Owner -eq "S-1-5-32-544")) { Throw 'Error: Powershell must be launched in elevated privileges mode' } # GET THE NAME OF THE WINDOWS SERVICE AND THE SQL CONNECTION if($sql_instance_name -eq "MSSQLSERVER") # DEFAULT INSTANCE { $service_name = 'MSSQLSERVER' $sql_server_instance = "." } else # NAMED SQL INSTANCE { $service_name = "MSSQL`$$sql_instance_name" $sql_server_instance = ".\$sql_instance_name" } Write-Information "SQL Server: $sql_server_instance" Write-Information "Serivce Name: $service_name" Write-Information "" # GET THE SERVICE OBJECT AND THE DEPENDENT SERVICES $sql_service = Get-Service -Name $service_name $dependent_services = $sql_service.DependentServices # # EXTRA CHECK: STOP IF THE SERVICE IS NOT FOUND if(-Not ($sql_service)) { Throw "Error: Please ensure the sql instance $sql_instance_name is valid and a windows service with name $service_name exists..." } $msg = "Service Status: " + ($sql_service.Status) Write-Information $msg $msg = "Service Startup Type: " + $sql_service.StartType Write-Information $msg Write-Information "" # IF THE SERVICE IS DISABLED, PROMPT TO RE-ENABLE IT IN MANUAL MODE 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: Script cannot continue when SQL Instance is in Disabled mode." } } # STOP THE SERVICE ONLY IF IT IS RUNNING # PROMPT TO CONFIRM BEFORE STOPPING THE SERVICE if($sql_service.Status -eq 'Running') { Write-Warning "Stopping service: $service_name" Write-Warning "Any dependent services will also be stopped..." if ($confirm) {Stop-Service -InputObject $sql_service -Confirm -Force} else {Stop-Service -InputObject $sql_service -Force} Write-Information "" Write-Information "STOP-SERVICE MAY RUN IN ASYNC MODE SO LETS SLEEP FOR FEW SECONDS..." Start-Sleep 5 # CHECK TO MAKE SURE THE SERVICE IS NOW STOPPED $sql_service.Refresh() if($sql_service.Status -ne "Stopped") { throw "Error: SQL instance service $service_name must be in stopped state before continuing...." } } # A WINDOWS SERVICE CAN ONLY BE STARTED IF IT'S START UP TYPE IS MANUAL, AUTOMATIC OR DELAYED AUTOMATIC START # SO, CONTINUE ONLY IF THE START UP TYPE IS NOT ONE OF THEM $sql_service.Refresh() if($sql_service.Status -ne 'Running' -and $sql_service.StartType -in ('Manual', 'Automatic')) { Write-Warning "Starting SQL Service in single user mode..." Write-Information "" net start $service_name /f /m Start-Sleep 2 # CHECK TO MAKE SURE THE INSTANCE IS NOW RUNNING $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; ALTER SERVER ROLE sysadmin ADD MEMBER $login_to_be_granted_access; " } else { $sql = "CREATE LOGIN [$login_to_be_granted_access] WITH PASSWORD=N'$sql_login_password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF" $sql += "; ALTER SERVER ROLE sysadmin ADD MEMBER $login_to_be_granted_access; " } $msg = "Adding $login_to_be_granted_access to the SYSADMIN role in SQL Server..." Write-Information $msg sqlcmd.exe -E -S $sql_server_instance -Q $sql Write-Information "" Write-Information "Restarting the SQL instance in normal mode..." net stop $service_name net start $service_name # Restart any dependenT service that were running... foreach ($dependent_service in $dependent_services) { if($dependent_service.Status -eq 'Running') { $dependent_service_name = $dependent_service.Name # Check one more time to make sure it's not already running... if ((Get-Service -Name $dependent_service_name).Status -ne 'Running') { $msg = "Starting dependent service: $dependent_service_name" Write-Information $msg $dependent_service.Start() } } } } else { Throw "Error: SQL Instance is not running...." } }
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 "add user to sql sysadmin role.ps1, then using the Invoke-Command, I can run it on a remote server (highlighted):
$script_file_path = "add user to sql sysadmin role.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 = "add user to sql sysadmin role.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.