Problem
In previous tips of this series (tip 1, tip 2 and tip 3) I presented a couple of tools that can help you to perform the failover of databases under your care, along with the status of each of the databases. However, just being able to successfully failover a database (or a set of databases) doesn’t mean that your job is 100% done, you still have to deal with logins and jobs on the failover server and in this tip we will focus on logins.
Solution
This tip targets reporting about login differences using a PowerShell script. It is very important to mention that this PowerShell script will only give you the output of the status of the logins between the Principal and Mirror instances. With this in mind, you probably already have a pretty good idea of what’s going on and should plan carefully the course of action that you will take to fix any differences.
PowerShell Script to Find Mismatched Logins for SQL Server Database Mirroring
Here’s the complete code, along with some considerations, usage and outputs.
- The script receives 1 input as a parameter, which is the SQL Server instance you want to check.
- Since this is targeted for being used in an environment with database mirroring in place, its logic is built around that premise.
- You can enter either the Principal or the Mirror instance to obtain the information for your setup.
- You might need to modify the Invoke-Sqlcmd commands to enter a specific set of credentials to establish the database connection.
if ($args[0].Length -gt 0){
$server = $args[0]
#Attempt to connect to the SQL Server instance and exit the whole thing if it is not successful
try{$test = Invoke-Sqlcmd -Query "SELECT 1" -ServerInstance $server -EA SilentlyContinue}
catch{
Write-Host "/*"$args[0]"is not a valid instance, please check and try again... */"
break
}
#If the connection to the instance is successful, then attempt to retrieve the logins information
$hasMirroring = Invoke-Sqlcmd -Query "SELECT COUNT(*) AS 'value' FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL" -ServerInstance $server
if ($hasMirroring.value -gt 0){
#Missing Logins Section#
####################################################################################################################
Write-Host "######################################################################"
Write-Host "####################### MISSING LOGINS SECTION #######################"
Write-Host "######################################################################"
Write-Host ""
$roleDeterminationQuery = "
SELECT DISTINCT mirroring_role
FROM sys.database_mirroring
WHERE mirroring_role IS NOT NULL
"
$loginsQuery = "
SELECT *
FROM sys.syslogins
WHERE name NOT LIKE '%##' AND name NOT LIKE '%NT SERVICE\%' AND name NOT LIKE '%NT AUTHORITY\%'
ORDER BY loginname
"
$partnerQuery = "
SELECT DISTINCT mirroring_partner_instance
FROM sys.database_mirroring
WHERE mirroring_partner_instance IS NOT NULL
"
$serverRole = Invoke-Sqlcmd -Query $roleDeterminationQuery -ServerInstance $server
$partner = Invoke-Sqlcmd -Query $partnerQuery -ServerInstance $server
if($serverRole.mirroring_role -ne 1){
$temp = $server
$server = $partner['mirroring_partner_instance']
$partner['mirroring_partner_instance'] = $temp
}
$instanceLogins = @(Invoke-Sqlcmd -Query $loginsQuery -ServerInstance $server)
$partnerInstanceLogins = @(Invoke-Sqlcmd -Query $loginsQuery -ServerInstance $partner['mirroring_partner_instance'])
$resultMissing = @(Compare-Object -ReferenceObject $instanceLogins.loginname -DifferenceObject $partnerInstanceLogins.loginname)
$resultEqual = @(Compare-Object -ReferenceObject $instanceLogins.loginname -IncludeEqual $partnerInstanceLogins.loginname)
$missingLoginsAtPrincipal = @()
$missingLoginsAtMirror = @()
$matchingLogins = @()
foreach ($login in $resultMissing){
if ($login.SideIndicator -eq "=>"){ $missingLoginsAtPrincipal += $login.InputObject }
if ($login.SideIndicator -eq "<="){ $missingLoginsAtMirror += $login.InputObject }
}
foreach ($login in $resultEqual){
if ($login.SideIndicator -eq "=="){ $matchingLogins += $login.InputObject }
}
if($missingLoginsAtPrincipal.Length -gt 0){
$header = "Missing Logins at "+$server
$line = ""
Write-Host $header
for($i = 0; $i -lt $header.Length; $i++){ $line += "-" }
Write-Host $line
$missingLoginsAtPrincipal
Write-Host ""
}
if($missingLoginsAtMirror.Length -gt 0){
$header = "Missing Logins at "+$partner['mirroring_partner_instance']
$line = ""
Write-Host $header
for($i = 0; $i -lt $header.Length; $i++){ $line += "-" }
Write-Host $line
$missingLoginsAtMirror
}
if(($missingLoginsAtPrincipal.Length -eq 0) -and ($missingLoginsAtMirror.Length -eq 0)){
Write-Host "Nothing to show here..."
}
Write-Host ""
#Roles Mismatch between logins Section#
####################################################################################################################
Write-Host "######################################################################"
Write-Host "############### ROLES MISMATCH BETWEEN LOGINS SECTION ###############"
Write-Host "######################################################################"
foreach ($login in $matchingLogins){
foreach ($instanceLogin in $instanceLogins){
if($login -eq $instanceLogin.loginname){
foreach ($partnerInstanceLogin in $partnerInstanceLogins){
if($instanceLogin.loginname -eq $partnerInstanceLogin.loginname){
if($instanceLogin.sysadmin -ne $partnerInstanceLogin.sysadmin){
Write-Host "ROLE : SYSADMIN"
Write-Host "LOGIN :"$login
Write-Host "INSTANCE:"$server "VALUE:"$instanceLogin.sysadmin
Write-Host "INSTANCE:"$partner['mirroring_partner_instance'] "VALUE:"$partnerInstanceLogin.sysadmin
Write-Host "######################################################################"
}
if($instanceLogin.securityadmin -ne $partnerInstanceLogin.securityadmin){
Write-Host "ROLE : SECURITYADMIN"
Write-Host "LOGIN :"$login
Write-Host "INSTANCE:"$server "VALUE:"$instanceLogin.securityadmin
Write-Host "INSTANCE:"$partner['mirroring_partner_instance'] "VALUE:"$partnerInstanceLogin.securityadmin
Write-Host "######################################################################"
}
if($instanceLogin.serveradmin -ne $partnerInstanceLogin.serveradmin){
Write-Host "ROLE : SERVERADMIN"
Write-Host "LOGIN :"$login
Write-Host "INSTANCE:"$server "VALUE:"$instanceLogin.serveradmin
Write-Host "INSTANCE:"$partner['mirroring_partner_instance'] "VALUE:"$partnerInstanceLogin.serveradmin
Write-Host "######################################################################"
}
if($instanceLogin.setupadmin -ne $partnerInstanceLogin.setupadmin){
Write-Host "ROLE : SETUPADMIN"
Write-Host "LOGIN :"$login
Write-Host "INSTANCE:"$server "VALUE:"$instanceLogin.setupadmin
Write-Host "INSTANCE:"$partner['mirroring_partner_instance'] "VALUE:"$partnerInstanceLogin.setupadmin
Write-Host "######################################################################"
}
if($instanceLogin.processadmin -ne $partnerInstanceLogin.processadmin){
Write-Host "ROLE : PROCESSADMIN"
Write-Host "LOGIN :"$login
Write-Host "INSTANCE:"$server "VALUE:"$instanceLogin.processadmin
Write-Host "INSTANCE:"$partner['mirroring_partner_instance'] "VALUE:"$partnerInstanceLogin.processadmin
Write-Host "######################################################################"
}
if($instanceLogin.diskadmin -ne $partnerInstanceLogin.diskadmin){
Write-Host "ROLE : DISKADMIN"
Write-Host "LOGIN :"$login
Write-Host "INSTANCE:"$server "VALUE:"$instanceLogin.diskadmin
Write-Host "INSTANCE:"$partner['mirroring_partner_instance'] "VALUE:"$partnerInstanceLogin.diskadmin
Write-Host "######################################################################"
}
if($instanceLogin.dbcreator -ne $partnerInstanceLogin.dbcreator){
Write-Host "ROLE : DBCREATOR"
Write-Host "LOGIN :"$login
Write-Host "INSTANCE:"$server "VALUE:"$instanceLogin.dbcreator
Write-Host "INSTANCE:"$partner['mirroring_partner_instance'] "VALUE:"$partnerInstanceLogin.dbcreator
Write-Host "######################################################################"
}
if($instanceLogin.bulkadmin -ne $partnerInstanceLogin.bulkadmin){
Write-Host "ROLE : BULKADMIN"
Write-Host "LOGIN :"$login
Write-Host "INSTANCE:"$server "VALUE:"$instanceLogin.bulkadmin
Write-Host "INSTANCE:"$partner['mirroring_partner_instance'] "VALUE:"$partnerInstanceLogin.bulkadmin
Write-Host "######################################################################"
}
}
}
}
}
}
Write-Host ""
#Orphan Users#
####################################################################################################################
Write-Host "######################################################################"
Write-Host "############################ ORPHAN USERS ############################"
Write-Host "######################################################################"
Write-Host ""
Write-Host "Principal Instance:"$server
$databasesQuery = "
SELECT name
FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')
"
$databases = Invoke-Sqlcmd -Query $databasesQuery -ServerInstance $server
foreach($database in $databases){
$orphanUsersQuery = "
SELECT name as 'login'
FROM sys.database_principals
WHERE sid NOT IN (SELECT sid FROM master.sys.server_principals)
AND type_desc != 'DATABASE_ROLE' AND name NOT IN ('guest','dbo','INFORMATION_SCHEMA','sys')
"
$orphanUsers = Invoke-Sqlcmd -Query $orphanUsersQuery -ServerInstance $server -Database $database.name
if($orphanUsers.login.length -gt 0){
$line = ""
Write-Host ""
Write-Host "DATABASE:"$database.name
Write-Host "LOGIN :"$orphanUsers.login
for($i = 0; $i -lt 15+$database.name.Length; $i++){ $line += "#" }
Write-Host $line
}
}
Write-Host ""
}
else{
Write-Host "/* Database mirroring is not configured in this SQL Server instance */"
}
}
else{
Write-Host "/* Please enter an instance as a parameter to proceed */"
}
Write-Host "Done!"
Expected Output of PowerShell Script
To demonstrate the possible outputs that the script can display, I have configured database mirroring within 2 test instances (without a witness to keep things simple) and will show you each of them.
- Principal Instance: MC0Z5A9C\TEST2
- Mirror Instance: MC0Z5A9C\TEST1
Missing SQL Server Logins
I created a SQL login called "test2" on MC0Z5A9C\TEST2 only.
With this information, you can evaluate if you need this login on the other instance. Remember that you might be dealing with a shared SQL Server instance, or even worse, you have inherited a shared SQL Server instance and have no idea of what belongs and what doesn’t.
Server Roles Mismatch Between Logins
I created a SQL login called "test" on both SQL Server instances, with a slight difference:
- The login at MC0Z5A9C\TEST2 has the serveradmin role assigned.
- The login at MC0Z5A9C\TEST1 has the sysadmin role assigned.
Here’s the output of the script for that particular scenario:
The output shows any inconsistencies for each login between the 2 instances and the server roles. It is up to you to evaluate each case and determine the correct one.
Orphaned Users on the Principal Instance
I have 2 databases with database mirroring configured between them (db1 & db2):
- In db1 I have created a SQL user, called test1, without a login.
- In db2 I have created a SQL user, called test2, without a login.
Since the Mirror databases are not in an accessible state, this section of the script is executed only against the Principal instance. After a successful failover, it can be executed on the other instance to see if there are any issues.
With this information, you can then determine if you need to fix these cases or if they can just be ignored or cleaned up.
Next Steps
- There are already a ton of useful resources regarding logins and users within MSSQLTips; just keep in mind that the focus of this approach leans toward database mirroring.
- Possibly a v2.0 of this script will integrate the option to automatically fix all the inconsistencies reported. At the moment, the information presented by the script in its current state is helpful enough (under the assumption that you are not doing this already with another tool or mechanism).
- The next tip will focus on the status of the jobs for each SQL Server instance participating in the database mirroring setup, so stay tuned!
- Check out the related tips:
Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.
- MSSQLTips Awards: Author of the Year – 2020 | Rising Star (50+ tips) – 2021 | Author Contender – 2019, 2021