Use Powershell to Pick Up what Database Mirroring Leaves Behind

Database mirroring has been around since SQL Server 2005, and it’s turned out to be an excellent step up from log shipping.  However, like log shipping, it is still only a database-level disaster recovery solution.  Meaning that any logins, server role memberships or server-level permissions will not be mirrored over to the mirror server.  This is where the DBA needs to plan ahead and create their own custom jobs to script and/or document these types of shortcomings.

My solution is to use Powershell.  In this example, I have setup database mirroring for the AdventureWorks2012 database.  For this demo, both instances, TEST1 and TEST2, are on the same physical server.

There are two logins on the principal server that currently do not exist on the mirror server.  One is a SQL login, AWLogin1, and the other is a Windows Authenticated login, TRON2AWLogin2.

The first step of our Powershell script will need to connect to the principal server to generate a CREATE LOGIN script for those two logins.  To generate the script, we need to grab the login name, the SID, and the hashed password if it’s a SQL login.  This is accomplished by running the following code.

SELECT 'USE master; CREATE LOGIN ' + QUOTENAME(p.name) + ' ' +
CASE WHEN p.type in ('U','G')
    THEN 'FROM WINDOWS '
    ELSE ”
    END
+ 'WITH ' +
CASE WHEN p.type = 'S'
    THEN 'PASSWORD = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' HASHED, ' + 'SID = ' + master.sys.fn_varbintohexstr(l.sid) +  ', CHECK_EXPIRATION = ' +
    CASE WHEN l.is_expiration_checked >0
        THEN 'ON, '
        ELSE 'OFF, '
        END
    + 'CHECK_POLICY = ' +
    CASE WHEN l.is_policy_checked >0
        THEN 'ON, '
        ELSE 'OFF, '
        END +
    CASE WHEN l.credential_id > 0
        THEN 'CREDENTIAL = ' + c.name + ', '
        ELSE ”
        END
ELSE ”
END
+ 'DEFAULT_DATABASE = ' + p.default_database_name+
CASE WHEN LEN(p.default_language_name)> 0
    THEN ', DEFAULT_LANGUAGE = '+ p.default_language_name
    ELSE ”
    END
+ ';' AS 'LoginScript'
FROM master.sys.server_principals p LEFT JOIN master.sys.sql_logins l
    ON p.principal_id = l.principal_id LEFT JOIN master.sys.credentials c
    ON l.credential_id = c.credential_id
WHERE p.type IN ('S','U','G')
    AND p.name NOT IN ('sa', 'NT AUTHORITYSYSTEM')
    AND p.name NOT LIKE '##%##'
    AND p.name NOT LIKE 'BUILTIN%'
    AND p.name NOT LIKE 'NT SERVICE%'
ORDER BY p.name;

In this example, you can see we have one row for each of the two logins.

The next step of the Powershell script will need to write those two rows of data to a file on the mirror server.  This is done using the System.IO.StreamWriterclass.

foreach($row in $commandList.Tables[0].Rows)
{
	try
	{
		$output = $row["LoginScript"].ToString()
		$stream.WriteLine($output)
	}
	catch
	{
		$stream.Close()
		CheckForErrors
	}
}

When there is a need to failover to the mirror server, the DBA can then open this script and run it.  All logins will be created and with their original SID value and password.

The second half of the Powershell script will use the same procedures to script out any server role memberships or server-level permissions these two logins may have on the principal server.  This is done using the following block of code.

-- BUILD SERVER ROLE MEMBERSHIPS
SELECT 'USE master; EXEC sp_addsrvrolemember @loginame = '+QUOTENAME(s.name)+', @rolename = '+QUOTENAME(s2.name)+';'  AS 'ServerPermission'
FROM master.sys.server_role_membersr INNER JOIN master.sys.server_principals s
    ON s.principal_id = r.member_principal_id INNER JOIN master.sys.server_principals s2
    ON s2.principal_id = r.role_principal_id
WHERE s2.type = 'R'
    AND s.is_disabled = 0
    AND s.name NOT IN ('sa','NT AUTHORITYSYSTEM')
    AND s.name NOT LIKE '##%##'
    AND s.name NOT LIKE 'NT SERVICE%'
UNION ALL
-- BUILD SERVER-LEVEL PERMISSIONS
SELECT 'USE master; '+sp.state_desc+' '+sp.permission_name+' TO '+QUOTENAME(s.name) COLLATE Latin1_General_CI_AS+';'  AS 'ServerPermission'
FROM sys.server_permissions sp JOIN sys.server_principals s
    ON sp.grantee_principal_id= s.principal_id
WHERE s.type IN ('S','G','U')
    AND sp.type NOT IN ('CO','COSQ')
    AND s.is_disabled = 0
    AND s.name NOT IN ('sa','NT AUTHORITYSYSTEM')
    AND s.name NOT LIKE '##%##'
    AND s.name NOT LIKE 'NT SERVICE%';

From the output, you can see the TRONAWLogin2 is a member of the BULKADMIN server role and has the VIEW SERVER STATE permission.  These two rows will be written to a file in the same file share as the previous file. 

As before, once the database is failed over to the mirror server, the DBA can run this script to apply any missing permissions.

Finally, this Powershell script can be scheduled to run from any server; however, I choose to setup this job on the principal server.  I schedule it to run once a day through SQL Agent.  Each run of the script will overwrite the existing file, so if there are any logins or permissions that have been added or removed, it will show up in the latest version of the files.

Using this Powershell script can make it very easy to script out logins and permissions.  While this example was used with database mirroring, then same strategy will work for log shipping.  The entire Powershell script is below.

########################################################################################### 
#
#   File Name:    CopyLoginsPermissions.ps1
#
#   Applies to:   SQL Server 2008
#                 SQL Server 2008 R2
#                 SQL Server 2012
#
#   Purpose:      To copy logins and permissions from a PRIMARY server
#                 to it's corresponding MIRROR server.
#
#   Prerequisite: Powershell v2.0 must be installed.
#                 SQL Server components must be installed.
#
#   Parameters:   [string]$sourceInstance - Production SQL Server name (Ex: SERVER\INSTANCE)
#                 [string]$destinationPath - BCP backup share (UNC Path Ex: \\SERVER\backup$)
#
#   Author:       Patrick Keisler
#
#   Version:      1.0.0
#
#   Date:         03/24/2013
#
#   Help:         http://www.patrickkeisler.com/
#
###########################################################################################
 
#Enable Debug Messages
#$DebugPreference = "Continue"
 
#Disable Debug Messages
$DebugPreference = "SilentlyContinue"
 
#Terminate Code on All Errors
$ErrorActionPreference = "Stop"
 
#Clear screen
CLEAR
 
#Load Assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null
 
function CheckForErrors {
    $errorsReported = $False
    if($Error.Count -ne 0)
    {
  Write-Host
  Write-Host "******************************"
        Write-Host "Errors:" $Error.Count
        Write-Host "******************************"
        foreach($err in $Error)
        {
            $errorsReported  = $True
            if( $err.Exception.InnerException -ne $null)
            {
                Write-Host $err.Exception.InnerException.ToString()
            }
            else
            {
                Write-Host $err.Exception.ToString()
            }
            Write-Host
        }
        throw;
    }
}
function GetServer {
    Param([string]$serverInstance)
 
    $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")($serverInstance)
    $server.ConnectionContext.ApplicationName = "Copy Logins and Permissions"
 $server.ConnectionContext.ConnectTimeout = 5
    $server;
}
function GetServerLogins {
 Param([string]$serverInstance, [string]$destinationPath)
  
 $array = $serverInstance.Split("\")
 if($array.Length -eq 1)
 {
  $machineName = $array[0]
  $instanceName = "DEFAULT"
 }
 else
 {
  $machineName = $array[0]
  $instanceName = $array[1]
 }
  
 $fileName = $destinationPath + "\" + $machineName + "_" + $instanceName + "_ServerLogins.sql"
 $stream = New-Object System.IO.StreamWriter($fileName)
 
 $server = GetServer($serverInstance)
 $db = $server.Databases["master"]
 $commandList = $db.ExecuteWithResults(
 @"
SELECT 'CREATE LOGIN ' + QUOTENAME(p.name) + ' ' +
CASE WHEN p.type in ('U','G')
        THEN 'FROM WINDOWS '
        ELSE ''
        END
+ 'WITH ' +
CASE WHEN p.type = 'S'
        THEN 'PASSWORD = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' HASHED, ' + 'SID = ' + master.sys.fn_varbintohexstr(l.sid) +  ', CHECK_EXPIRATION = ' +
        CASE WHEN l.is_expiration_checked > 0
                THEN 'ON, '
                ELSE 'OFF, '
                END
        + 'CHECK_POLICY = ' +
        CASE WHEN l.is_policy_checked > 0
                THEN 'ON, '
                ELSE 'OFF, '
                END +
        CASE WHEN l.credential_id > 0
                THEN 'CREDENTIAL = ' + c.name + ', '
                ELSE ''
                END
ELSE ''
END
+ 'DEFAULT_DATABASE = ' + p.default_database_name +
CASE WHEN LEN(p.default_language_name) > 0
        THEN ', DEFAULT_LANGUAGE = ' + p.default_language_name
        ELSE ''
        END
+ ';' AS 'LoginScript'
FROM master.sys.server_principals p LEFT JOIN master.sys.sql_logins l
 ON p.principal_id = l.principal_id LEFT JOIN master.sys.credentials c
 ON l.credential_id = c.credential_id
WHERE p.type IN ('S','U','G')
 AND p.name NOT IN ('sa','NT AUTHORITY\SYSTEM')
 AND p.name NOT LIKE '##%##'
 AND p.name NOT LIKE 'BUILTIN\%'
 AND p.name NOT LIKE 'NT SERVICE\%'
ORDER BY p.name;
"@
 )
  
 Write-Host "Writing to server logins file..." -NoNewline
 $stream.WriteLine("/* This file should not be blank!!!*/")
 $stream.WriteLine("/* These commands were generated from: $serverInstance */")
 
 if ($commandList.Tables[0].Rows.Count -ge 1)
 {
  foreach($row in $commandList.Tables[0].Rows)
  {
      try
      {
    $debug = "Script: " + $row["LoginScript"]
    Write-Debug $debug
    $output = $row["LoginScript"].ToString()
    $stream.WriteLine($output)
   }
   catch
   {
    $stream.Close()
    CheckForErrors
   }
  }
 }
 $stream.close()
 Write-Host "OK"
}
 
function GetServerPermissions {
 Param([string]$serverInstance, [string]$destinationPath)
  
 $array = $serverInstance.Split("\")
 if($array.Length -eq 1)
 {
  $machineName = $array[0]
  $instanceName = "DEFAULT"
 }
 else
 {
  $machineName = $array[0]
  $instanceName = $array[1]
 }
  
 $fileName = $destinationPath + "\" + $machineName + "_" + $instanceName + "_ServerPermissions.sql"
 $stream = New-Object System.IO.StreamWriter($fileName)
 
 $server = GetServer($serverInstance)
 $db = $server.Databases["master"]
 $commandList = $db.ExecuteWithResults(
 @"
-- BUILD SERVER ROLE MEMBERSHIPS
SELECT 'USE master; EXEC sp_addsrvrolemember @loginame = '+QUOTENAME(s.name)+', @rolename = '+QUOTENAME(s2.name)+';'  AS 'ServerPermission'
FROM master.sys.server_role_members r INNER JOIN master.sys.server_principals s 
 ON s.principal_id = r.member_principal_id INNER JOIN master.sys.server_principals s2 
 ON s2.principal_id = r.role_principal_id 
WHERE s2.type = 'R' 
 AND s.is_disabled = 0 
 AND s.name NOT IN ('sa','NT AUTHORITY\SYSTEM') 
 AND s.name NOT LIKE '##%##' 
 AND s.name NOT LIKE 'NT SERVICE\%' 
UNION ALL
-- BUILD SERVER-LEVEL PERMISSIONS
SELECT 'USE master; '+sp.state_desc+' '+sp.permission_name+' TO '+QUOTENAME(s.name) COLLATE Latin1_General_CI_AS+';'  AS 'ServerPermission' 
FROM sys.server_permissions sp JOIN sys.server_principals s 
 ON sp.grantee_principal_id = s.principal_id 
WHERE s.type IN ('S','G','U') 
 AND sp.type NOT IN ('CO','COSQ') 
 AND s.is_disabled = 0 
 AND s.name NOT IN ('sa','NT AUTHORITY\SYSTEM') 
 AND s.name NOT LIKE '##%##' 
 AND s.name NOT LIKE 'NT SERVICE\%';
"@
 )
  
 Write-Host "Writing to server permissions file..." -NoNewline
 $stream.WriteLine("/* If this file is blank, then no server-level permissions (i.e Server Role Memberships) existed on the production server. */")
 $stream.WriteLine("/* These commands were generated from: $serverInstance */")
  
 if ($commandList.Tables[0].Rows.Count -ge 1)
 {
  foreach($row in $commandList.Tables[0].Rows)
  {
      try
      {
    $debug = "Script: " + $row["ServerPermission"]
    Write-Debug $debug
    $output = $row["ServerPermission"].ToString()
    $stream.WriteLine($output)
   }
   catch
   {
    $stream.Close()
    CheckForErrors
   }
  }
 }
 $stream.close()
 Write-Host "OK"
}
 
function PerformValidation {
    Param($sourceInstance, $destinationPath)
  
 Write-Host "Validating parameters..." -NoNewline
    if([String]::IsNullOrEmpty($sourceInstance))
    {
  Write-Host "ERROR"
     $errorMessage = "Source server name is not valid."
        throw $errorMessage
    }
    if([String]::IsNullOrEmpty($destinationPath))
    {
  Write-Host "ERROR"
        $errorMessage = "Destination path name is not valid."
     throw $errorMessage
    }
 else
    {
        if(-not($destinationPath.StartsWith("\\")))
        {
   Write-Host "ERROR"
            $errorMessage = "Destination path is not valid: " + $destinationPath
            throw $errorMessage
        }
    }
     
 Write-Host "OK"
  
 Write-Host "Verifying source SQL Server connectivity..." -NoNewline
 $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($sourceInstance)
    $conn.ApplicationName = "Copy Logins and Permissions"
 $conn.NonPooledConnection = $true
 $conn.ConnectTimeout = 5
 try
 {
  $conn.Connect()
        $conn.Disconnect()
 }
 catch
 {
  CheckForErrors
 }
    Write-Host "OK"
 
 
    Write-Host "Verifying destination file share exists..." -NoNewline
    if((Test-Path -Path $destinationPath) -ne $True)
    {
  Write-Host "ERROR"
        $errorMessage = "File share: " + $destinationPath + " does not exists"
        throw $errorMessage
    }
    Write-Host "OK"
}
 
function Main{
  
 Param([string]$sourceInstance, [string]$destinationPath)
  
 $Error.Clear()
 
    Write-Host
    Write-Host "============================================================="
    Write-Host " 1: Perform Initial Checks & Validate Input Parameters"
    Write-Host "============================================================="
  
 PerformValidation $sourceInstance $destinationPath
  
    Write-Host
    Write-Host "============================================================="
    Write-Host " 3: Create Server Logins Script"
    Write-Host "============================================================="
 
 GetServerLogins $sourceInstance $destinationPath
 
    Write-Host
    Write-Host "============================================================="
    Write-Host " 4: Create Server Permissions Script"
    Write-Host "============================================================="
 
 GetServerPermissions $sourceInstance $destinationPath
 
    Write-Host
    Write-Host "============================================================="
    Write-Host "  Copy Logins and Permissions to DR Completed Successfully   "
    Write-Host "============================================================="
}
 
#Capture inputs from the command line.
$sourceInstance = $args[0]
$destinationPath = $args[1]
 
#Hard-coded values used only for development
#$sourceInstance = "TRON\TEST1"
#$destinationPath = "\\TRON\BACKUP1"
 
#Prompt for inputs for an interactive script
#$sourceInstance = $(Read-Host "Source SQL Server name (Ex: server\instance)")
#$destinationPath = $(Read-Host "Destination share to copy backup file to (UNC Path Ex: \\SERVER\backup$)")
 
$debug = "Source Instance Parameter: " + $sourceInstance
Write-Debug $debug
$debug = "Destination Path Parameter: " + $destinationPath
Write-Debug $debug
 
Main $sourceInstance $destinationPath
Share