We had a user recently who wanted to quickly see which users had access to which companies and the Security Roles which had been granted. While Dynamics GP does have a number of security reports, they are the standard ones which, while readable in the usual screen or printer output, cannot easily be exported to Excel. However, the client is on Microsoft Dynamics GP 2013 R2 and therefore has access to SmartList Designer.
While I could have done this entirely as a SmartList Designer report, I already had the majority of the SQL needed to generate this as a SQL View which SmartList Designer can access:
CREATE VIEW uv_PI_UserAccessAndGrantedSecurityRoles AS SELECT ['User Master'].USERID AS 'User ID' ,['User Master'].USERNAME AS 'Username' ,['User Master'].USRCLASS AS 'User Class' ,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description' ,ISNULL(['Company Master'].INTERID, '') AS 'Intercompany ID' ,ISNULL(['Company Master'].CMPNYNAM, '') AS 'Company Name' ,ISNULL(['Security Assignment User Role'].SECURITYROLEID, '') AS 'Security Role ID' ,ISNULL(['Security Roles Master'].SECURITYROLENAME, '') AS 'Security Role Name' FROM SY01400 AS ['User Master'] LEFT JOIN SY40400 AS ['Class Master'] ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS LEFT JOIN SY60100 AS ['User-Company Access'] ON ['User-Company Access'].USERID = ['User Master'].USERID LEFT JOIN SY10500 AS ['Security Assignment User Role'] ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID LEFT JOIN SY09100 AS ['Security Roles Master'] ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID LEFT JOIN SY01500 AS ['Company Master'] ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID GO
Of course, the other reason I used the SQL rather than recreating entirely in SmartList Designer is that I can use this SQL in future, but a SmartList Designer is only usable on the system on which it is created as there is no import/export functionality.
I was working onsite with a client during an upgrade of Microsoft Dynamics GP from version 10 to 2013 recently, and after doing some work on a client we started receiving an error when trying to log out..
The error we received was the following:
After a little testing to see if we could easily determine the problem, I did a quick online search and came up with this post from Vaidy Mohan which was the same error, but for a different VBA project.
The steps I tried and found to be successful were slightly shorter and easier than those Vaidy needed:
- Launch GP and open the Visual Basic Editor
- Opened modified report in the Microsoft Dynamics GP VBA project
- Added and removed a space from the end of one line
- Compiled the entire project to check for errors
- Save VBA project
- Log off from GP
To verify the problem was resolved we closed and reopened Dynamics GP and didn’t see the error when closing it again.
Over the course of the series I have run through the installation and configuration of several parts of Config AD, but there is still a lot of functionality that I haven’t covered.
For example, I have shown how to associate a GP user with a Windows AD account, but not how to disassociate them. I also didn’t show how SSRS security could also be assigned to users in Config AD; largely because I didn’t have SSRS installed and configured on my test box.
The main reason I haven’t covered even more than I have is that I enjoy playing around with different software and want to move onto something else. I’ve enjoyed the opportunity to have a go with Config AD which I found easy to install, easy to configure and that it provides a lot of functionality which brings together the security setup of Dynamics GP into one location.
Something I find very annoying in Dynamics GP is that to create a user, grant company access and assign roles you need to enter the System Password at least three times (unless you’re just copying security from another user wholesale), but Config AD allows you to configure all of this after logging into it once.
If you’re looking for an add-on which will both simplify the maintenance of Dynamics GP security (and I assume the effect would be the same for the other Dynamics products it integrates with) and allow for single sign on, then Config AD is definitely worth considering.
With the Config AD Desktop installed, we need to configure it for use; many of the steps in this section are only required the first time you run Config AD Desktop on a machine.
Start Config AD Desktop from the Windows Start Screen (or Start menu for those on an older version of Windows) and click on File >> options:
To install the Config AD Desktop run the setup.exe in the Config AD Desktop 2.1.3 folder and accept the security warning:
The Config AD Desktop requires that Config AD itself be installed. The installation on the client is the same as on the Domain Controller with one exception.
On the Add Active Directory Right-Click Menu tab set the option to Don’t add either menu:
With the service installed, the next stage is to configure the service. This is done by accessing Config AD and then opening the Config AD Options () and then select the Service tab:
On this tab there are several options which can be enabled/disabled:
- Disable/Delete Feature Enabled – When this option is enabled a choice can be made as to the interval the process cycles at and whether Dynamics GP users should be disabled or deleted when the AD account is deleted.
- Auto Logout Feature – This option will logout users after the specified number of minutes inactivity.
- AD Group Security – This option will provision new users and assign/revoke access to GP users based upon AD Group membership of AD Groups that are setup in Config AD for GP access
As well as the above options, you also need to specify the Login name that should be used to access SQL Server.
Config AD includes a service which can run on any machine within the network; the service can automatically log users out after a period of inactivity and will also disable/delete GP user accounts when the AD user is deleted.
A service account is needed to run the Config AD service; this account needs to be a Domain User account and also needs to be either a Domain Admin or have the following permissions:
- Read Property and List Contents permission to the Deleted Objects container in Active Directory (non-admin users will not have these permissions by default, but they can be granted: further details available from Microsoft Support).
- Read All Properties permission to the container(s) where your users are located.
- Log on as a service permission.
- A SQL Login on the SQL Server for the domain user.
With a suitable service account, find the Fastpath.ADServiceSetup.msi in the /FPConfigAD 220.127.116.11/Config AD Service 3.3.0 folder.
Once a user has been created or associated, the first time that user logs into GP they will need to link the GP and Windows passwords. When Dynamics GP is started, the user ID will default in; enter the password (if the user was created from Config AD the password will be set to the Config AD Options) and click OK: