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.

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.

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.

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



Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook