PrintVis Tip of the Week #29 – Adding Multiple Users to a Database with Windows Powershell

PrintVis Tip of the Week #29: Adding Multiple Users to a Database with Windows Powershell

This week Peter Tijsma reports back to us from Utrecht, The Netherlands, with a very specific script that provides a sweeping shortcut for adding multiple users to a newly-set up database, rather than encountering a common (and frustrating) error message.

From time to time you may find yourself in a situation where you receive a database from a customer (or a colleague) and, after nicely restoring the database and setting up a service-tier, you might get unhappily confronted with a message from the NAV Client saying:

PT1

It’s sometimes quite difficult to access to such a database, especially when it comes from a customer using a different Windows domain from the database you’ve restored. What a senseless and time-consuming setback to an otherwise routine step in implementation.

Thankfully our friend Powershell comes to the rescue yet again.

The script on this post will help you with this scenario – and whenever you simply need to add a bunch of users to a database.

Of course you could simply add a single user, then log in to the database and add new users manually from the NAV Client. But how tedious is that?  Why not do all of them at once with one script?

When set up correctly, this script will add the multiple users to the database – simultaneously – as Windows users and Database users.

Simply set these generic variables and run the script:

  • $UserAccounts
    Simply fill this comma-separated list with the usernames you want to add to the database
  • $GlobalPassword
    This is the generic password to use for all users that will be added as Database User (NavUserPassword login)
  • $Instance
    The Instance Name of the Service-Tier you created for the database
  • $DomainName
    The Windows Domain to be used for the Windows Users you want to create. Simply set this to $null if you don’t use this database in a domain, but rather in a local Workgroup
  • $CreateWindowsUsers
    Set this to $true to create the users as Windows Users in the Database, $false to skip this step
  • $CreateDatabaseUsers
    Set this to $true to create the users as Database Users (NavUserPassword login) in the Database, $false to skip this step

Have fun!

Thank you Peter – Some of us will!

Here's the Script

#region Set Generic Variables

$UserAccounts = @(‘user1’,‘user2’,‘user3’)

$GlobalPassword = ‘Pa$$w0rd’

$Instance = ‘DynamicsNAV90’

$DomainName = ‘OURDOMAIN’ # Set to $null if no domain is to be used

$CreateWindowsUsers = $true

$CreateDatabaseUsers = $true

#endregion

if (-not $DomainName -and $CreateWindowsUsers) {

$Computer = Get-WmiObject -Class Win32_ComputerSystem

$DomainName = $Computer.Name

}

Clear-Host

$securePassword = (ConvertTo-SecureString $GlobalPassword -AsPlainText -Force)

Write-Host “Adding users to Instance $Instance -ForegroundColor DarkYellow

foreach ($user in $UserAccounts) {

if($CreateDatabaseUsers) {

Write-Host “Adding database user $user …” -ForegroundColor Gray

New-NAVServerUser -ServerInstance $Instance -UserName $user `

-Password $securePassword -LicenseType Full -State Enabled -ErrorAction SilentlyContinue

New-NAVServerUserPermissionSet $Instance –UserName $user `

-PermissionSetId SUPER -ErrorAction SilentlyContinue

–    }

if ($CreateWindowsUsers) {

Write-Host “Adding windows user $DomainName\$user …” -ForegroundColor Gray

New-NAVServerUser -ServerInstance $Instance -WindowsAccount$DomainName\$user” `

-LicenseType Full -State Enabled -ErrorAction SilentlyContinue

New-NAVServerUserPermissionSet $Instance –WindowsAccount$DomainName\$user” `

-PermissionSetId SUPER -ErrorAction SilentlyContinue

}

}

Write-Host “Done adding users”