Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Mirroring Missing Logins Report


By:   |   Last Updated: 2019-03-15   |   Comments   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Database Mirroring

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.

desktop

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:

sysadmin

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.

orphan users

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


Last Updated: 2019-03-15


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools