Search code examples
sql-serverpowershellsmo

powershell smo login move

Is it possible to create a Powershell script that moves the login from a source server to a destination server - including the current password? I tried to use the sp_help_revlogin procedure, but I'm unable to get the t-sql output into a variable.

$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $LoginDes = $event.Message };
$smoSrc.connectionContext.add_InfoMessage($handler);
$smoSrc.connectionContext.executeNonQuery("EXEC master..sp_help_revlogin $dbLogin")

If a try to use the script() method, the code generated has the password field but is not the sid value.

Solution

  • Passwords have been troublesome with powershell historically, but you can get the SID if you use the script method and pass in a custom ScriptingOptions object.

    $so = new-object microsoft.sqlserver.management.smo.scriptingoptions;
$so.LoginSid = $true
$login.script( $so );