We use cookies to improve your experience.
By your continued use of this site you accept such use.
For more details please see our privacy policy and cookies policy.

Script Repository

Check whether Employee ID is present in SQL Database

February 18, 2021 Views: 3566

This PowerShell script checks whether an Employee ID specified for a new user is present in the user's record from an SQL HR database.

To use the script, you need to create a business rule triggered automatically on user creation. For more details, see Validate/Modify User Input Using a Script.


  • $databaseHost - Specifies the fully qualified domain name or IP address of the database host.
  • $databaseName - Specifies the database name.
  • $tableName - Specifies the name of the database table that contains information on employee IDs.
  • $fieldName - Specifies the name of the database field to search employee IDs in.
  • $databaseUsername - Specifies the username to be used to connect to the database.
  • $databasePassword - Specifies the database password.
To use credentials of the Adaxes service account when connecting to the database, set both $databaseUsername and $databasePassword to $NULL.
Note: The script uses cmdlets from Adaxes PowerShell module for Active Directory. To run the script, you need to install the PowerShell Module for Active Directory component of Adaxes.
Edit Remove
Import-Module Adaxes
$databaseHost = "host.company.com" #TODO: modify me
$databaseName = "MyDatabase" #TODO: modify me
$tableName = "UsersTable" #TODO: modify me
$fieldName = "EmployeeID" #TODO: modify me

$databaseUsername = $NULL #TODO: modify me
$databasePassword = $NULL #TODO: modify me

$employeeIdSpecified = $Context.GetModifiedPropertyValue("employeeID")
if ($employeeIdSpecified -eq $NULL)
    $Context.Cancel("Employee ID not specified.") # TODO: modify me

# Search for the Employee ID in the SQL database
$connectionString = "Data Source=$databaseHost; Initial Catalog=$databaseName;"
if ($databaseUsername -eq $NULL)
    $connectionString = $connectionString +
        "Integrated Security=SSPI;"
    $connectionString = $connectionString +
        "User ID=$databaseUsername;Password=$databasePassword;"

    $connection = New-Object "System.Data.SqlClient.SqlConnection"  $connectionString

    $command = $connection.CreateCommand()
    $command.CommandText = "SELECT $fieldName FROM $tableName WHERE $fieldName = $employeeIdSpecified;"
    $employeeIDFromSQL = $command.ExecuteScalar()

    if ($employeeIDFromSQL -eq $NULL)
        $Context.Cancel("You have entered an invalid Employee ID.") # TODO: modify me

    # Search AD for a user with the specified Employee ID
    $domainName = $Context.GetObjectDomain("%distinguishedName%")
    $user = Get-AdmUser -Filter 'employeeID -eq $employeeIDFromSQL' `
        -Server $domainName -AdaxesService localhost

    if ($user -ne $NULL)
        $Context.Cancel("The specified Employee ID already exists in Active Directory.") # TODO: modify me
    # Release resources used by the SQL query and the AD search

Comments 0
Leave a comment

Got questions?

Support Questions & Answers