综合技术

SQL Server Database Mirroring Missing Logins Report

微信扫一扫,分享到朋友圈

SQL Server Database Mirroring Missing Logins Report
0

By:Alejandro Cobar |   Last Updated: 2019-03-15   |  |   Related Tips:More > Database Mirroring

Problem

In previous tips of this series (tip 1,tip 2 andtip 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 withlogins andjobs 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: MC0Z5A9CTEST2
  • Mirror Instance: MC0Z5A9CTEST1

Missing SQL Server Logins

I created a SQL login called "test2" on MC0Z5A9CTEST2 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 MC0Z5A9CTEST2 has the serveradmin role assigned.
  • The login at MC0Z5A9CTEST1 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

Last Updated: 2019-03-15

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

View all my tips

Related Resources

阅读原文...


微信扫一扫,分享到朋友圈

SQL Server Database Mirroring Missing Logins Report
0

MSSQLTips

雷军:“5G+AI+IoT”是下一代超级互联网

上一篇

[AST实战]从零开始写一个wepy转VUE的工具

下一篇

评论已经被关闭。

插入图片

热门分类

往期推荐

SQL Server Database Mirroring Missing Logins Report

长按储存图像,分享给朋友