Tuesday, October 15, 2013

A Power shell script for updating admin user SID and other details

Often I will load up the AX demo database under a different domain. I have write the following PowerShell script to help modify the admin user SID, login info, etc in AX. It uses Invoke-Sqlcmd SQL Server cmdlet to update the USERINFO table.
                
$networkDomain = "[domain]"     # e.g. yourDomain.com
$networkAlias = "[user alias]"  
$name = "[user name]"                
$dbname = "[your AX DB Name]"             
$sqlServer = "localhost"             

$targetUserId = "admin"         # Change this to update other existing AX users

$objUser = New-Object System.Security.Principal.NTAccount("$networkDomain","$networkAlias")
$sid = $objUser.Translate([System.Security.Principal.SecurityIdentifier])

$sqlstmt = "UPDATE {5}.dbo.USERINFO SET SID = '{0}', NETWORKDOMAIN = '{1}', NETWORKALIAS = '{2}', Name = '{3}' " +
        "WHERE ID = '{4}'"
$sqlstmt = [string]::Format($sqlstmt,$sid, $networkDomain, $networkAlias, $name, $targetUserId, $dbname)

Invoke-Sqlcmd -Query $sqlstmt -ServerInstance $sqlServer
Since I am using this on an all in one DEV box, the Invoke-Sqlcmd command-let is readily available. If Invoke-SqlCmd is not recognized in the machine you want to run the script, try the tips from this link.

This posting is provided "AS IS" with no warranties, and confers no rights.

3 comments:

  1. Thanks for sharing, Dominic! :-)

    ReplyDelete
  2. Nice post!
    But how could I change all my migrated users from one domain to another?
    I want to change networkDomain with similar automated cool solution.

    ReplyDelete
    Replies
    1. First of all, play with Get-ADUser to get only the AX users from AD. Then, modify the script above into a function. Once you have both, pipeline them..
      e.g.
      Get-ADUser -Filter [your filter] | foreach-Object { UpdateAXUser($_.SID, $_.SamAccountName) }

      Delete