{"id":148,"date":"2012-04-26T07:32:35","date_gmt":"2012-04-26T06:32:35","guid":{"rendered":"http:\/\/www.darrencoupland.com\/blog\/?p=148"},"modified":"2012-08-15T22:09:56","modified_gmt":"2012-08-15T21:09:56","slug":"removing-user-login-data-from-non-production-environments","status":"publish","type":"post","link":"https:\/\/www.darrencoupland.com\/blog\/2012\/04\/removing-user-login-data-from-non-production-environments\/","title":{"rendered":"Removing User Login Data from Non-Production Environments"},"content":{"rendered":"

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

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)<\/p>\n

The user log data can be viewed in AX2009 via ‘Administration > Inquiries > User Log’<\/p>\n

Initially run this query to determine the AX DB creation date:<\/p>\n

SELECT create_date\r\nFROM\r\nsys.databases\r\nWHERE\r\nname =  '<Your AX Database Name>'<\/pre>\n
Once you’ve verified the date is okay run the below script:<\/p>\n
\/* Delete User Login Data based upon Database Creation Date *\/\r\nDECLARE @dbcreatedate DATETIME\r\n\r\n\/* Get Database Creation Date *\/\r\nSELECT @dbcreatedate = create_date\r\nfrom\r\nsys.databases\r\nWHERE name = '<Your AX Database Name>'\r\n\r\nSELECT @dbcreatedate AS Database_Creation_Date\r\n\r\n\/* Delete User Login Data *\/\r\nDELETE FROM SysUserLog\r\nWHERE\r\nCREATEDDATETIME < @dbcreatedate<\/pre>\n
Download\u00a0\n\tDelete-User-Login-Data-using-Database-Creation-Date\t(6544 downloads\t)\n<\/a>\n<\/p>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n
This script in this post will delete user log data prior to the database creation date (e.g. everything prior to your non-prod restore date)<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[3],"tags":[29,8,27,25,28],"class_list":["post-148","post","type-post","status-publish","format-standard","hentry","category-dynamics-ax-2009","tag-administration","tag-ax2009","tag-nonproduction","tag-scripts","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts\/148","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/comments?post=148"}],"version-history":[{"count":1,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts\/148\/revisions"}],"predecessor-version":[{"id":556,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts\/148\/revisions\/556"}],"wp:attachment":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/media?parent=148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/categories?post=148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/tags?post=148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}