sp_help_revlogin Alternative

sp_help_revlogin, as I'm sure most readers will know, is a stored procedure that scripts logins on an instance of SQL Server. It is popular because, in addition to scripting Windows logins, it can script SQL logins without changing the SID or password. This is very useful for database migrations and refreshing development database copies.

It could be easier to use though. Using the mirroring terminology of principal and mirror (one of the uses of this procedure):

It is necessary to first install the stored procedure on the principal instance. It is not installed with SQL Server by default.
Then open a query window on the instance, set current database to master, select Output as Text and run sp_help_revlogin
The output scripts ALL logins on the principal. You have to edit the script to remove existing logins, or accept that you will see errors when it runs.
It won't generate any ALTER LOGIN statements, or look at role membership.

How can this be improved?

Only script logins that don't already exist on the mirror.
Add the new logins to server roles if necessary
Drop and recreate logins if the SID is different.
Script ALTER LOGIN statements if the password or default database are different.
Should be clever enough to detect if the default database does not exist on the mirror, or is not online.
Script ALTER USER statements in each database if the SIDs do not match (prevents orphaned users).
Create database users and add to database roles if necessary.
Run from your workstation without having to explicitly connect to either instance (until you run the resulting script of course).

OK, you've probably guessed where this is leading. Look at this script:

param ([string]$Principal, [string]$Mirror) 
[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 
        } 
        $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) 
                        { 
                                "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" 
                        } 
                        "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)';" 
                } 
        } 
        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)';" 
        } 
}

The logic for this is very similar to sp_help_revlogin, but it connects to both servers and does a hell of a lot more.

Most of my clients are still using PowerShell 1.0, so that's what I've used. This should work without modification in v2.0.

Copy this into a text file, and save as "ScriptLoginDiffs.ps1".
From within PowerShell, run the script as follows:

The output is displayed to the console, but can be redirected to a file as follows:

I doubt if I will need to use sp_help_revlogin again.

Not found what you're looking for?

Use this search box. It is tuned for SQL Server searches. Try it and see!

Do you have a question about SQL Server? Would you like to answer a question?

Go to the SQL FAQ to get started.

sp_help_revlogin Alternative -> SQL Server Scripts

sp_help_revlogin Alternative -> SQL Server Pro Home

sp_help_revlogin Alternative

Navigation