Technical Article

Script Login Differences

,

Script Login Differences

I wrote this script a couple of years ago and find it really useful, so it's about time I shared it with the rest of you!

Often you find yourself wanting to copy logins and permissions between servers. I'll use the terms principal and mirror for the rest of this description, as one of the most common uses is to keep logins in step when mirroring.

Years ago, Microsoft published a stored procedure called sp_help_revlogin to assist with this. This is useful, but it has a few drawbacks:

  • It scripts all logins on the principal, without checking if they already exist on the mirror;
  • It doesn't generate ALTER LOGIN statements;
  • It doesn't look at server role membership or server permissions;
  • It has to be installed on the principal before it can be used - some companies have rules preventing stored procedures from being added to the master database;

My script has a few advantages:

  • It compares the logins on principal and mirror and generates a script containing DROP, ALTER and CREATE statements to bring them in line;
  • It generates GRANT, REVOKE and DENY statements;
  • It keeps the passwords for SQL logins the same, unless the -KeepPwd switch is specified;
  • If the default database for a login does not exist on the mirror, or is offline, it specifies master instead;
  • It compares the SIDs in each database with the newly created logins, and corrects them if different, to prevent orphaned users;

Copy the script into a file called ScriptLoginDiffs.ps1.

From Powershell (any version) navigate to the same folder and run the following:

.\ScriptLoginDiffs.ps1 PRINCIPALSERVERNAME MIRRORSERVERNAME

If you are concerned there may be SQL logins on the mirror where the password should not be changed, use this syntax:

.\ScriptLoginDiffs.ps1 PRINCIPALSERVERNAME MIRRORSERVERNAME -KeepPwd

Don't worry, it doesn't make any changes. The output is a script that can be run against the mirror. To capture the output into a .sql file to open in SSMS, use the Tee command:

.\ScriptLoginDiffs.ps1 PRINCIPALSERVERNAME MIRRORSERVERNAME | Tee OutputFileName.sql

I hope you find this useful.

It won't work if either instance is SQL Server 2000 or earlier, and I haven't tested against databases with 80 compatibility level.

param ([string]$Principal, [string]$Mirror, [switch]$KeepPwd) 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null 
$ErrorActionPreference = "Stop" 
$ps = New-Object Microsoft.SqlServer.Management.Smo.Server $Principal 
$ms = New-Object Microsoft.SqlServer.Management.Smo.Server $Mirror 
if (-not $ps.Databases.Count) 
{ 
        "Could not connect to $Principal" 
        return 
} 
if (-not $ms.Databases.Count) 
{ 
        "Could not connect to $Mirror" 
        return 
} 
"-------------------------------------------------------" 
"-- The following script should be run on $Mirror." 
"-------------------------------------------------------" 
foreach ($login in $ps.Logins) 
{ 
        if ("sa", "NT AUTHORITY\SYSTEM", "BUILTIN\ADMINISTRATORS" -contains $login.Name) 
        { 
                continue 
        } 
        if ($login.Name -match '##') 
        { 
                continue 
        } 
        $psid = "" 
        if ("WindowsGroup", "WindowsUser" -notcontains $login.LoginType) 
        { 
                $login.Sid | % {$psid += ("{0:X}" -f $_).PadLeft(2, "0")} 
                [byte[]] $phash = $ps.Databases["master"].ExecuteWithResults("select hash=cast(loginproperty('$($login.Name)', 'PasswordHash') as varbinary(256))").Tables[0].Rows[0].Hash
                $ppwd = "" 
                $phash | % {$ppwd += ("{0:X}" -f $_).PadLeft(2, "0")} 
        } 
        $defaultDatabase = "master" 
        $ms.Databases[$login.DefaultDatabase] | ? {$_.Status -eq "Normal"} | % {$defaultDatabase = $_.Name} 
        $dropped = $false 
        if ($ms.Logins[$login.Name]) 
        { 
                if ("WindowsGroup", "WindowsUser" -notcontains $login.LoginType) 
                { 
                        $msid = "" 
                        $ms.Logins[$login.Name].Sid | % {$msid += ("{0:X}" -f $_).PadLeft(2, "0")} 
                        if ($psid -ne $msid) 
                        { 
                                "drop login [$($login.Name)];" 
                                $dropped = $true 
                        } 
                        [byte[]] $mhash = $ms.Databases["master"].ExecuteWithResults("select hash=cast(loginproperty('$($login.Name)', 'PasswordHash') as varbinary(256))").Tables[0].Rows[0].Hash
                        $mpwd = "" 
                        $mhash | % {$mpwd += ("{0:X}" -f $_).PadLeft(2, "0")} 
                        if (-not $dropped -and $ppwd -ne $mpwd -and -not $KeepPwd) 
                        { 
                                "alter login [$($login.Name)] with password = 0x$ppwd hashed;" 
                        } 
                } 
                if (-not $dropped -and $ms.Logins[$login.Name].DefaultDatabase -ne $defaultDatabase) 
                { 
                        "alter login [$($login.Name)] with default_database = [$defaultDatabase];" 
                } 
        } 
        
        if (-not $ms.Logins[$login.Name] -or $dropped) 
        { 
                if ("WindowsGroup", "WindowsUser" -contains $login.LoginType) 
                { 
                        "create login [$($login.Name)] from windows with default_database = [$defaultDatabase];" 
                } 
                else 
                { 
                        if ($login.PasswordExpirationEnabled) 
                        { 
                                $checkExpiration = "on" 
                        } 
                        else 
                        { 
                                $checkExpiration = "off" 
                        } 
                        if ($login.PasswordPolicyEnforced) 
                        { 
                                $checkPolicy = "on" 
                        } 
                        else 
                        { 
                                $checkPolicy = "off" 
                        } 
if ($KeepPwd -and $ms.Logins[$login.Name])
{
                        "create login [$($login.Name)] with password = 0x$mpwd hashed, sid = 0x$psid, default_database = [$defaultDatabase], check_policy = $checkPolicy, check_expiration = $checkExpiration;"
}
else
{
                        "create login [$($login.Name)] with password = 0x$ppwd hashed, sid = 0x$psid, default_database = [$defaultDatabase], check_policy = $checkPolicy, check_expiration = $checkExpiration;"
}
                        if ($login.DenyWindowsLogin) 
                        { 
                                "deny connect sql to [$($login.Name)];" 
                        } 
                        if (-not $login.HasAccess) 
                        { 
                                "revoke connect sql to [$($login.Name)];" 
                        } 
                        if ($login.IsDisabled) 
                        { 
                                "alter login [$($login.Name)] disable;" 
                        } 
                } 
        } 
        foreach ($role in $ps.Roles | ? {$_.Name -ne "public"}) 
        { 
                $addRole = $false 
                if (-not $ms.Logins[$login.Name]) 
                { 
                        $addRole = $login.IsMember($role.Name) 
                } 
                elseif ($dropped -or -not $ms.Logins[$login.Name].IsMember($role.Name)) 
                { 
                        $addRole = $login.IsMember($role.Name) 
                } 
                if ($addRole) 
                { 
                        "exec sp_addsrvrolemember @loginame = N'$($login.Name)', @rolename = N'$($role.Name)';" 
                } 
        } 
        
$permsql = @"
select per.state_desc collate database_default + ' ' + per.permission_name collate database_default + ' TO [' + pri.name collate database_default + '];' perm_text
from sys.server_permissions per
join sys.server_principals pri on per.grantee_principal_id = pri.principal_id
where class_desc = 'SERVER'
and pri.name collate database_default = '$($login.Name)'
"@
$psperms = $ps.Databases["master"].ExecuteWithResults($permsql).Tables[0].Rows
$msperms = $ms.Databases["master"].ExecuteWithResults($permsql).Tables[0].Rows
$mspermcol = @()
foreach ($msp in $msperms)
{
$mspermcol += $msp.perm_text
}

foreach ($psp in $psperms)
{
$addPerm = $false
                if (-not $ms.Logins[$login.Name]) 
                { 
                        $addRole = $true
                }
                elseif ($dropped -or $mspermcol -notcontains $psp.perm_text)
                { 
                        $addRole = $true
                } 
                if ($addRole -and $psp.perm_text -notmatch "GRANT CONNECT SQL") 
                { 
                        $psp.perm_text
                } 
}

        foreach ($db in $ms.Databases | ? {$_.Status -eq "Normal"}) 
        { 
                $user = $null 
                $user = $db.Users | ? {$_.Login -eq $login.Name} 
                if ($user) 
                { 
                        if ($psid -ne "") 
                        { 
                                $usid = "" 
                                $user.Sid | % {$usid += ("{0:X}" -f $_).PadLeft(2, "0")} 
                                if ($usid -ne $psid) 
                                { 
                                        "use $($db.Name); alter user [$($user.Name)] with login = [$($login.Name)];" 
                                } 
                        } 
                } 
        } 
} 
#if in principal databases, but not on mirror, add user 
$principalUsers = $ps.Databases | 
        ? {$_.Status -eq "Normal"} | 
        % {$db = $_.Name; $_.Users | ? {$_.Login -ne ""} | 
        Select @{n="Database";e={$db}}, Name, Login} 
$mirrorUsers = $ms.Databases | 
        ? {$_.Status -eq "Normal"} | 
        % {$db = $_.Name; $_.Users | ? {$_.Login -ne ""} | 
        Select @{n="Database";e={$db}}, Name, Login} 
foreach ($user in $principalUsers) 
{ 
        if (-not ($ms.Databases | ? {$_.Status -eq "Normal" -and $_.Name -eq $user.Database})) 
        { 
                continue 
        } 
        if ($ms.Databases[$user.Database].Users[$user.Name]) 
        { 
                continue 
        } 
        if (-not $ps.Logins[$user.Login]) 
        { 
                continue 
        } 
        "use [$($user.Database)]; create user [$($user.Name)] for login [$($user.Login)];" 
} 
#if in principal databases, but not on mirror, add to roles 
$principalDbRoles = $ps.Databases | 
        ? {$_.Status -eq "Normal"} | 
        % {$db = $_.Name; $roles = $_.Roles; $roles | 
                % {$role = $_.Name; $_.EnumMembers() | ? {-not $roles[$_]} | 
                        Select @{n="Database";e={$db}}, @{n="Role";e={$role}}, @{n="Member";e={$_}}}} 
$mirrorDbRoles = $ms.Databases | 
        ? {$_.Status -eq "Normal"} | 
        % {$db = $_.Name; $roles = $_.Roles; $roles | 
                % {$role = $_.Name; $_.EnumMembers() | ? {-not $roles[$_]} | 
                        Select @{n="Database";e={$db}}, @{n="Role";e={$role}}, @{n="Member";e={$_}}}} 
        $lookup = @{} 
$mirrorDbRoles | % {$lookup.$($_.Database + ":" + $_.Role + ":" + $_.Member) = 1} 
foreach ($dbRole in $principalDbRoles) 
{ 
        if (-not $lookup.ContainsKey("$($dbRole.Database):$($dbRole.Role):$($dbRole.Member)")) 
        { 
                if (-not ($ms.Databases | ? {$_.Status -eq "Normal" -and $_.Name -eq $dbRole.Database})) 
                { 
                        continue 
                } 
                "use [$($dbRole.Database)]; exec sp_addrolemember N'$($dbRole.Role)', N'$($dbRole.Member)';" 
        } 
}

Rate

4.6 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

4.6 (5)

You rated this post out of 5. Change rating