Tag Archives: scripts

Updating an AX users SID when they’ve been accidently deleted from Active Directory

If you ever find an AX user’s account has been accidently deleted from Active Directory then is recreated with the same user name but they cannot access AX this is because they now have a new Active Directory SID that doesn’t match the SID against their user in AX.

This can be resolved by updating their SID in AX with the newly recreated user’s SID.

 

The Problem:

“User1” was deleted in Active Directory, it was established that “User1” shouldn’t have been deleted, so “User1” was recreated.  Unfortunately “User1” couldn’t access AX though, they were receiving an Access Denied message.

Resolution:

First of all run the following powershell script (replace “User1” with your user) to find their SID

$AdObj = New-Object System.Security.Principal.NTAccount('User1')
$strSID = $AdObj.Translate([System.Security.Principal.SecurityIdentifier])
$strSID.Value

 

As you can see their SID is returned as S-1-5-21-1214440339-1788223648-682003330-48397

 

Using the SID returned by the Powershell script run the following SQL Script (replacing the  Database Name, SID and ID with yours) to update the newly created login’s SID

UPDATE [MY_DAX09_DATABASE].[dbo].[USERINFO]
SET SID = 'S-1-5-21-1214440339-1788223648-682003330-48397'
WHERE ID = 'User1'

Note: ID is not usually the same as the users AD login name, in this example it happens to be.  Doing a simple select statement on the [UserInfo] table will establish the users ID that you need to use in the above SQL Statement.

“User1” will now be able to access AX again.

Get notified when Admin or All user group permissions are granted to users in AX 2009

Want to know instantly when a user has been granted ‘Admin’ or ‘All’  user rights in your Production or Non-Production environments?

Of course you do!  Implementing the script available to download below gives you control over your AX Environment with regards to knowing when permissions could have been granted incorrectly.

The script will create a trigger on the [USERGROUPLIST] table, if the user group ‘Admin’ or ‘All’ is added to a users profile then an email notification will be sent to your AX Administrators.

Follow the instructions within the SQL Script on what needs to be changed to configure it in your environment.  As always please setup and test in your Dev/Test Environment to ensure you understand the setup and that it works as intended in your environment.

I also always advise sending the email notifications to a distribution email group as opposed to individually named email addresses as it means you only need to change the distribution email group for Administrator leavers/starters as opposed to having to change numerous pieces of code/jobs/notifications.

Download Admin User Group Check Setup (264 downloads)

AX 2009 Batch Job Created Notifications

Want to know instantly by email when users are creating batch jobs in AX 2009.  This saves you having to constantly go and check the Batch Job form and work out what’s new.

I have created a script which can be downloaded below which will create a SQL DML insert trigger on your database, so when a user submits a process to batch you can receive an email informing you of the submitter and name of job they have submitted.

There’s a couple of pre-requisites that need to be adhered to:

1.  Database Mail must be configured and working in your environment
2.  You need to know the following details:

  • Your Database Server and Instance Name (Instance Name only applicable if you are using named instances)
  • Your Production Database Name
  • Your Database Mail Profile name
  • Email Recipients Address (always best to have a generic distribution group email address)

With these details you can perform a find and replace on the defaults values in the script before running the script against your AX Database, full details are provided in the script itself on how to set it up.

Download  AX Batch Jobs Added Script (241 downloads)

DISCLAIMER: As always please try setting this up first in your test environment to ensure you understand the setup and whether it provides the functionality you require.

Modifying Batch Job Statuses in a Non-Production Environment

There’s a few things you can do when undertaking your data refresh into a non-production environment one of them is modifying your batch jobs to ‘Withhold’ status ensuring they won’t start to run when you after you start up your AOS’s and then configure your batch server settings. After the script has been ran you can set the status of jobs you want to run back to ‘Waiting’ status at a later point during the data refresh process, happy in the knowledge that you know no batch jobs are going to start emailing customers or update/talk to other external systems.

SQL Script:

-- Change to correct DB
 USE <Your Ax Database>
-- Set all Batch Jobs to Withhold status
 UPDATE BatchJob
 SET status = 0

You could also have a scenario where only certain batch jobs should be set to Withhold status, you could do this by adding the batch jobs you want to continue to run into a table and then only update the ones not in that table to Withhold. An example script to do this can be found below:

Pre-Requisites: Create a table with one data field called ‘Caption’, populate table with batch job names that you want to continue to run in your non-production environment (e.g. not be at withhold status)

SQL Script:

-- Change to correct DB
USE <Your Ax Database>

UPDATE BatchJob
SET status = 0
WHERE
Caption NOT IN (SELECT Caption FROM <the table name created in pre-requisites>)

Disclaimer: Ensure any batch jobs you add to this table don’t have other parameters that need to be changed prior to them running again in the non-production environment.

Removing Email Addresses from Non-Production Environments

A few simple scripts to run when building a 2009 non-production environment from your 2009 production database to remove email addresses from the ‘CustTable’, ‘VendTable’ and ‘Address’ table this will ensure no emails can be sent in error to your customers or vendors from your non-production environment.

-- Change to correct DB
USE <Your Ax Database>

UPDATE Address
SET EMAIL = ''
WHERE
EMAIL != ''

UPDATE VendTable
SET EMAIL = ''
WHERE
EMAIL != ''

UPDATE CustTable
SET EMAIL = ''
WHERE
EMAIL != ''

DISCLAIMER: You may need to add additional tables/fields for any customizations you may be using.

Removing User Login Data from Non-Production Environments

As part of updating your AX 2009 non-production environments I recommend running a script to remove user login data so you can see who is using your environment and it doesn’t cloud things with it having LIVE login data in there.

This script will delete user log data prior to the database creation date (which should be the restored date of the database if a RESTORE DATABASE WITH REPLACE was used)

The user log data can be viewed in AX2009 via ‘Administration > Inquiries > User Log’

Initially run this query to determine the AX DB creation date:

SELECT create_date
FROM
sys.databases
WHERE
name =  '<Your AX Database Name>'

Once you’ve verified the date is okay run the below script:

/* Delete User Login Data based upon Database Creation Date */
DECLARE @dbcreatedate DATETIME

/* Get Database Creation Date */
SELECT @dbcreatedate = create_date
from
sys.databases
WHERE name = '<Your AX Database Name>'

SELECT @dbcreatedate AS Database_Creation_Date

/* Delete User Login Data */
DELETE FROM SysUserLog
WHERE
CREATEDDATETIME < @dbcreatedate

Download  Delete-User-Login-Data-using-Database-Creation-Date (519 downloads)

Run a Dynamics AX SQL statement as an API Cursor in SSMS

We all know that AX2009 uses cursors extensively – simply executing a SQL statement in SSMS doesn’t always result in the same query plan being used that an AX generated SQL statement would use.

Want to run a query in SQL Management Studio exactly how Dynamics AX 2009 runs the query e.g. as a API Cursor so you can ensure you are looking at the same query plan AX has used then run the query below.

Simply replace the red text below with all the query variables and replace the blue text with your parameters and execute the full statement and voila you can view the exact query plan AX uses to execute the statement.

TIP: If your unsure of the variables to declare in the cursor you can find them by doing a SQL Profiler Trace.

———————————————-
— Normal FFO Cursor
———————————————-

— Script taken from AX Dynamics Performance Analyzer scripts by Rod Hansen (http://code.msdn.com/dynamicsperf)

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int

— Fast Forward(16)+Parameterized(4096)+AutoFetch(8192)+AutoClose(16384)

set @p5=16+4096+8192+16384
declare @p6 int
set @p6=8193
declare @p7 int

— Number of Rows for AutoFetch.
— This is calculated by Maximum Buffer Size (24K default) / Row Length

set @p7=4
exec sp_cursorprepexec @p1 output,@p2 output,N’@P1 nvarchar(5),@P2 nvarchar(21)‘,N’SELECT A.SALESID,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND (SALESID>@P2))’,@p5 output,@p6 output,@p7 output,N’ceu’,N’SO-100004′
— @p2 contains cursor handle for fetch call
exec sp_cursorfetch @p2,2,1,@p7