SQL View To Select User, Company and Security Role Assignment

Microsoft Dynamics GPWe 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 'Secuity 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.

Error 800AC351 While Saving Microsoft Dynamics GP

Microsoft Dynamics GPI 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:

Microsoft Dynamics GP - Unknown error 0 - 800AC351 occurred saving the project fileMicrosoft Dynamics GP – Unknown error 0 – 800AC351 occurred saving the project file

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:

  1. Launch GP and open the Visual Basic Editor
  2. Opened modified report in the Microsoft Dynamics GP VBA project
  3. Added and removed a space from the end of one line
  4. Compiled the entire project to check for errors
  5. Save VBA project
  6. 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.

Implementing Fastpath’s Config AD: Conclusion

FastpathIn this series of posts I have taken a look at the Config AD product from Fastpath which adds single sign-on to the Dynamics GP desktop client; you can find my series index here.

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.

Click to show/hide the Implementing Fastpath's Config AD Series Index

Implementing Fastpath’s Config AD: Configuring Config AD Desktop

Microsoft Dynamics GPIn this series of posts I’m going to take a look at the Config AD product from Fastpath which adds single sign-on to the Dynamics GP desktop client; you can find my series index here.

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:

Fastpath Config AD Desktop

Continue reading

Implementing Fastpath’s Config AD: Installing Config AD Desktop

In this series of posts I’m going to take a look at the Config AD product from Fastpath which adds single sign-on to the Dynamics GP desktop client; you can find my series index here.

To install the Config AD Desktop run the setup.exe in the Config AD Desktop 2.1.3 folder and accept the security warning:

Open File - Security Warning

Continue reading

Implementing Fastpath’s Config AD: Installing Config AD Desktop Prerequisites

In this series of posts I’m going to take a look at the Config AD product from Fastpath which adds single sign-on to the Dynamics GP desktop client; you can find my series index here.

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:

Fastpath Configurator AD: Add Active Directory Right-Click Menu

Click to show/hide the Implementing Fastpath's Config AD Series Index

Implementing Fastpath’s Config AD: Configuring The Service

In this series of posts I’m going to take a look at the Config AD product from Fastpath which adds single sign-on to the Dynamics GP desktop client; you can find my series index here.

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 (File >> Options) and then select the Service tab:

On this tab there are several options which can be enabled/disabled:

  1. 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.
  2. Auto Logout Feature – This option will logout users after the specified number of minutes inactivity.
  3. 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.

Click to show/hide the Implementing Fastpath's Config AD Series Index

Implementing Fastpath’s Config AD: Installing The Service

FastpathIn this series of posts I’m going to take a look at the Config AD product from Fastpath which adds single sign-on to the Dynamics GP desktop client; you can find my series index here.

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:

  1. 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).
  2. Read All Properties permission to the container(s) where your users are located.
  3. Log on as a service permission.
  4. A SQL Login on the SQL Server for the domain user.

With a suitable service account, find the Fastpath.ADServiceSetup.msi in the /FPConfigAD 5.4.0.0/Config AD Service 3.3.0 folder.

Continue reading

Implementing Fastpath’s Config AD: First Login With Single Sign-On

In this series of posts I’m going to take a look at the Config AD product from Fastpath which adds single sign-on to the Dynamics GP desktop client; you can find my series index here.

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:

Welcome to Microsoft Dynamics GP

Continue reading

Implementing Fastpath’s Config AD: Install Single Sign-On

In this series of posts I’m going to take a look at the Config AD product from Fastpath which adds single sign-on to the Dynamics GP desktop client; you can find my series index here.

The point of Fastpath’s Config AD is to make life easier by removing the number of usernames and passwords a user needs to be entered to access Dynamics GP. In the previous posts in this series I have associated an exiting AD user with an existing Dynamics GP user and then created a new GP user from an AD one.

For the user to benefit from this integration we need to install some code into Dynamics GP. This is done by installing the Fastpath Single Sign-On. In the FPSSO folder double click the setup.exe. On the security warning click on Run:

Open File - Security Warning

Continue reading