SQL Server Using Powershell to Move Logins between Instances

Moving logins between instances is one of the critical migration activities. Let’s see how it can be done in powershell with the help of SMO.When transferring logins, two things need to be looked at.

1. Create logins which don’t exist at destination and skipping the existing ones.
2. Switch default database to master if logins default database doesn’t exists at destination instance.

Let’s begin with creating the server object of source and destination instance and their corresponding login objects.

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $SourceInstance
$DSrv = new-object('Microsoft.SqlServer.Management.Smo.Server') $destInstance
#get logins at destination instance
$dbs=$DSrv.Databases
$dsrvlogins=$Dsrv.Logins  
#get logins at source instance
$srvlogin=$srv.Logins

The login objects $srvlogin and $dsrvlogins contains detailed information of each login present in source and destination instance respectively.The next step is to loop through the logins present in source instance.

#loop through source logins
foreach($user in $srvLogin)
{
    $cntdb=0
    $cntlogin=0
    #iterate through destination database
    #change the logins default database to master if
    #default database doesn't exists at destination instance.
    foreach($db in $dbs)
    {
        if ($user.DefaultDatabase -eq $db.Name)
        {
            $cntdb=1
            break;
        }
    }
 if($cntdb -eq 0)
     {
        $ddb=$user.DefaultDatabase
        WriteToLog "Default Database $ddb doesn't exist at destination Instance $destInstance.Changing it to master!!!"
        $user.DefaultDatabase="master"
     }

The above code loops through each login and checks that the logins default database is present at the destination instance or not. If default database isn’t present, it replaces the logins default database with “master”.

The next step is to script out source instance login if it’s not present at destination instance.

#only logins which aren't present at destination instance
#are created.
#check if login exists at destination instance.
foreach($duser in $dsrvlogins)
{
    if($duser.Name -eq $user.Name)
    { 
        $cntlogin=1
        break;
    }
}
 #create login at destination instance if it doesn't exists.
 if( $cntlogin -eq 0)
 {
      WriteToLog $user.script()
      fn_ExecuteQuery $destInstance "master" $user.script()
 }

The above code iterates through destination instance logins and matches them against the source instance logins. It only creates logins which don’t exist at destination instance. The function WriteToLog writes the login script to a file and the function fn_ExecuteQuery executes the modified script on to the destination server. The #user.script() scripts out the login which is then passed to WriteToLog and fn_ExecuteQuery function.

The complete code is available here http://sdrv.ms/XsVAyg

 
Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook