One of the major migration activities is to fix orphan users.  Though, it can be easily fixed by sp_change_users_login procedure, however what if you need to do this for multiple servers. A powershell script comes in handy in this case.

Let’s start by creating a server and database object.

The next step is to iterate through and get orphaned users if any.

The above script iterates through all user and accessible databases of the instance. The $db.Users returns user details of all the users in a database. A user is said to be orphan if it has not been mapped to a login. The $user.Login property of user collection object contains empty string for orphan users.  The statement

$OrphanUser = $db.Users | Where-Object {$_.Login -eq “” -and $_.IsSystemObject -eq $False}

returns all non system orphan users. In case there are no orphaned users the above script moves to the next database in the database collection. The next step is to iterate through all orphaned users and fix them.

The above snippet iterates through all orphaned users. As this script only deals with orphaned users with same name as that login; the statement login = $s.logins | where-object {$ -eq $  -and $_.isdisabled -eq $False -and $_.IsSystemObject -eq $False -and $_.IsLocked -eq $False} checks whether a login with a similar name as orphaned user exists or not. If not, then it moves on to the next user. If a login exists, the script first logs the details into a log file and then executes the relevant query to fix the orphan user.  This completes the script.

The complete script 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