Fixing orphaned users

In Chapter 4, Basic SQL Server Administration, we talked about how we can list orphaned users in SQL Server. An orphaned user is a database user that is no longer mapped to a valid instance login. Using SMO, you may be tempted to do something like the following snippet:

#unfortunately this doesn't work
$user.Login = "JDoe";
$user.Alter();
$user.Refresh();

In the preceding script, we are simply assigning a new login to an SMO database user object and invoking the Alter() method. Syntactically and logically, this should work. However, it doesn't. In this case, we will need to resort to sending an actual ALTER T-SQL command to SQL Server to fix an orphaned user. The snippet that can accomplish this task is as follows:

$username = "kurapika"
$query = @"
  ALTER USER $($username)
  WITH LOGIN = $($login)
"@
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query

Once this code finishes executing, you can verify that the database user has indeed been mapped...