{"id":456,"date":"2015-10-08T06:31:31","date_gmt":"2015-10-08T06:31:31","guid":{"rendered":"http:\/\/www.exploreax.com\/blog\/?p=456"},"modified":"2015-10-08T06:31:31","modified_gmt":"2015-10-08T06:31:31","slug":"ax-db-restore-scripts-8-disable-users","status":"publish","type":"post","link":"http:\/\/www.exploreax.com\/blog\/blog\/2015\/10\/08\/ax-db-restore-scripts-8-disable-users\/","title":{"rendered":"AX DB Restore Scripts #8 – Disable users"},"content":{"rendered":"

A few weeks ago I embarked on a\u00a0series of posts<\/a> on SQL scripts\u00a0I\u00a0use 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.\u00a0This 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.<\/p>\n

Disabling users.<\/strong><\/p>\n

Quite often companies or partners will require\u00a0access 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.<\/p>\n

    \n
  1. Only provide a shortcut\/configuration to users who require it.<\/li>\n
  2. Color code your environments<\/a> so that they immediately recognizable.<\/li>\n
  3. Add a startup message to your AX environment to warn users what environment they are logged into.<\/li>\n<\/ol>\n

    All of these solutions do not physically stop users from accessing the alternate environment. \u00a0So 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.<\/p>\n

    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.\"DisableUser\"<\/a><\/p>\n

    The\u00a0accomplish the same in a SQL script you can do the following.\u00a0I have parameterised the SQL for easier reuse or adjustment.<\/p>\n

    --List of users separated by | to keep enabled, while disabling all others \r\nDeclare @ENABLE_USERS NVarchar(max) = '|Admin|TIM|'\r\n\r\nupdate userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @ENABLE_USERS) = 0\r\n\r\n\r\n--List of users separated by | to disable, while keeping all the rest enabled all others  \r\nDeclare @DISABLE_USERS NVarchar(max) = '|BOB|JANE|'\r\n\r\nupdate userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @DISABLE_USERS) > 0<\/pre>\n
    Note 1: Parameterising a list can be tricky in TSQL, so the above trick is what i found easiest.
    \nNote 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\u00a0what you expect.<\/p>\n
    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!<\/p>\n
    View Previous –\u00a0AX DB Restore Scripts #7 \u2013 Setting Email Templates and User Email Addresses<\/a><\/p>\n
    Back to List<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
    A few weeks ago I embarked on a\u00a0series of posts on SQL scripts\u00a0I\u00a0use 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.\u00a0This post will be the last in the set of “data cleanup scripts” designed to help scrub…Read more AX DB Restore Scripts #8 – Disable users<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5,47,88,45],"tags":[27,36,109,39,48,28,15],"_links":{"self":[{"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/posts\/456"}],"collection":[{"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/comments?post=456"}],"version-history":[{"count":1,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/posts\/456\/revisions"}],"predecessor-version":[{"id":458,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/posts\/456\/revisions\/458"}],"wp:attachment":[{"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/media?parent=456"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/categories?post=456"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/tags?post=456"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}