A few weeks ago I embarked on a series of posts on SQL scripts I use when restoring Dynamics AX databases from one environment to another. We have covered a variety of topics including various configuration and setting changes to Dynamics AX. This post will be the last in the set of “data cleanup scripts” designed to help scrub your data for use in a development or testing environment.
Quite often companies or partners will require access to development environments to be restricted to a very select group of users that are aware of what to test and are also careful enough to work methodically in the correct environments (i.e. not trying to to real work in the wrong environment or test work in production). To ensure all of the above one could follow a few paths.
- Only provide a shortcut/configuration to users who require it.
- Color code your environments so that they immediately recognizable.
- Add a startup message to your AX environment to warn users what environment they are logged into.
All of these solutions do not physically stop users from accessing the alternate environment. So if you need an additional security mechanism, simply disable the users either manually in AX after doing a restore or disable then along with the SQL scripts we have been building up over the past few weeks.
To disable users in AX navigate to “System Administration -> Common -> Users -> Users. Double click on the user in question, click “edit”, uncheck the “enabled” button.
The accomplish the same in a SQL script you can do the following. I have parameterised the SQL for easier reuse or adjustment.
--List of users separated by | to keep enabled, while disabling all others
Declare @ENABLE_USERS NVarchar(max) = '|Admin|TIM|'
update userinfo set userinfo.enable=0 where CharIndex('|'+ cast(ID as varchar) + '|' , @ENABLE_USERS) = 0
--List of users separated by | to disable, while keeping all the rest enabled all others
Declare @DISABLE_USERS NVarchar(max) = '|BOB|JANE|'
update userinfo set userinfo.enable=0 where CharIndex('|'+ cast(ID as varchar) + '|' , @DISABLE_USERS) > 0
Note 1: Parameterising a list can be tricky in TSQL, so the above trick is what i found easiest.
Note 2: You will need to decide whether the list of users provided is inclusive (i.e. provide the list of all the users you WANT to disable) or exclusive (i.e. disable all users except for a small subset). I have provided both options, but would recommend the exclusive approach as it is more likely to give you safely give you what you expect.
I hope this assists you and will be useful in your management of your AX environments. Keep an eye out for new posts in this series!
View Previous – AX DB Restore Scripts #7 – Setting Email Templates and User Email Addresses
Back to List
As you may be aware by now, AX allows one to create users of type “active directory group” which if setup will auto-create users who belong to that group when they try to login. Furthermore users (whether auto-created or manually created) who belong to these groups will inherit the security permissions assigned to these groups.
One challenge however is, to debug this. I.E. Finding out which users are members of specific groups or what groups a specific user belongs to. My previous post was about how to determine ownership via command line. After a bit of reflection I thought this may be better and more useful to have this functionality directly within AX. Using Attached is an XPO with the relevant code (use at your own risk).
Basically it adds a class to AX as well as a lookup menu item to UserListPage form and the User form.
Please let me know your comments.
Here is a sample job if you don’t want to download the full project. It prints all groups for a user.
static void adGroups(Args _args)
CLRObject groups, enum;
System.String domain, username1,groupName;
Userid userId = curUserId();
permission = new InteropPermission(InteropKind::CLRInterop);
yourDomain = new System.DirectoryServices.AccountManagement.PrincipalContext(System.DirectoryServices.AccountManagement.ContextType::Domain);
// find your user
userInfo = xUserInfo::find(false, userId);
domain = UserInfo.networkDomain;
username1 = UserInfo.networkAlias;
user = System.DirectoryServices.AccountManagement.UserPrincipal::FindByIdentity(yourDomain, username1);
// if found - grab its groups
if(user != null)
groups = user.GetAuthorizationGroups();
enum = groups.GetEnumerator();
p = enum.get_Current();
groupName = p.get_Name();
groupN = groupName;
As you may all be aware, AX 2012 offers certain ability to manage your Dynamics AX 2012 users via Active directory groups. Hopefully more on that later. (Check out this post in the meantime http://blogs.msdn.com/b/dynamics-coe/archive/2013/01/13/using-windows-ad-groups-for-user-management-in-ax2012.aspx)
The one problem however is that it becomes quite difficult to trouble shoot security settings. For example how to determine whether a user is actually part of an active directory user group. If you are not a domain admin you can try and use the following two command prompt instructions
1. To detemine an individual user’s groups
net user /domain [username]
e.g.: net user /domain jonathanhalland
2. To detemine all users belonging to a specific group
net group /domain [groupname]
e.g.: net group /domain axrequisitioncreators