SQL Server Using Powershell to Fix Orphan Users

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.

$inst="AHMAD-PC"
# Connect to the specified instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst
# get database collection 
$dbs = $s.Databases | where-object {$_.IsSystemObject -eq $FALSE -and $_.IsAccessible -eq $TRUE}

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

foreach ($db in $dbs) {
   $dbname=$db.Name
   #Get orphaned users.
   # users with login = "" are orphaned users
   $OrphanUser = $db.Users | Where-Object {$_.Login -eq "" -and $_.IsSystemObject -eq $False}
    
   #if there are no orphaned users in a database 
   #move to next database
   if(!$OrphanUser)
   {
        #No orphan user for database.
        WriteToLog "There are no orphan users for database $dbname"
         
   }

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.

foreach($user in $OrphanUser)
     {
        $username=$user.name
        #get login name with same name as that of orphaned user.
        $login = $s.logins | where-object {$_.name -eq $user.name  -and $_.isdisabled -eq $False -and $_.IsSystemObject -eq $False -and $_.IsLocked -eq $False}
         
        #if a login doesn't exists; move to next orphaned user
        if(!$login)
        {
            #login with same name doesn't exists.
            break;            
        }else
        {
            #fix orphan user.
            $query="ALTER USER " +  $username + " WITH LOGIN = " +  $username
            $database=$user.Parent.Name
            WriteToLog "Mapping user " + $database + "." + $username + " to " + $username
            #execute the query.
            ExecuteQuery $inst $database $query
             
        }

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 {$_.name -eq $user.name  -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 http://sdrv.ms/XVLF4s

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