Security Views For Use In SmartList Designer: Security Roles With Tasks & Operations

Microsoft Dynamics GPThe sixth SQL view in this series does not include users, but instead shows the security roles, tasks and also the operations. To use this view you will need to have populated the Security Resource Descriptions table.

CREATE VIEW [dbo].[uv_PI_SecurityRolesWithTasksAndOperations] AS
SELECT
	['Security Roles Master'].SECURITYROLEID AS 'Security Role ID'
	,['Security Roles Master'].SECURITYROLENAME AS 'Security Role Name'
	,['Security Role Task Assignment'].SECURITYTASKID AS 'Security Task ID'
	,['Security Task Master'].SECURITYTASKNAME AS 'Security Task Name'
	,['Security Resource Descriptions'].Series_Name AS 'Series Name'
	,['Security Resource Descriptions'].DSPLNAME AS 'Security Operation Name'
	,['Security Resource Descriptions'].OwnerSTR AS 'Security Operation Owner'
FROM
	SY09100 AS ['Security Roles Master']
INNER JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
INNER JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
INNER JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON ['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
INNER JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON ['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
			AND ['Security Resource Descriptions'].SECRESOwner = ['Security Assignment Task Operations'].SECRESOwner
GO

GRANT SELECT ON uv_PI_UserAccessAndGrantedSecurityRolesWithTasks TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Security Views For Use In SmartList Designer: Populating Security Resource Descriptions

Microsoft Dynamics GPIn this post I’m taking a slight diversion and not posting a SQL view. The reason is that the next view I will be posting requires that the security resource descriptions be available which, by default, they’re not. However, they can be made available, which is what I will be covering in this post.

SY09400 is the Security Resource Descriptions table which is, by default, empty as the names are stored within the Microsoft Dynamics GP client itself. However, this table can be populated, perhaps somewhat counter-intuitively, by using the Clear Data window (Microsoft Dynamics GP menu >> Maintenance >> Clear Data).

To populate the table, open Clear Data, click on Display and then select Physical:

Clear Data

Continue reading

Security Views For Use In SmartList Designer: Security Roles With Tasks

Microsoft Dynamics GPThe fourth view being posted in this series takes a step back from the users and just shows the role and tasks within.

CREATE VIEW [dbo].[uv_PI_SecurityRolesWithTasks] AS
SELECT
	['Security Roles Master'].SECURITYROLEID AS 'Security Role ID'
	,['Security Roles Master'].SECURITYROLENAME AS 'Security Role Name'
	,['Security Role Task Assignment'].SECURITYTASKID AS 'Security Task ID'
	,['Security Task Master'].SECURITYTASKNAME AS 'Security Task Name'
FROM
	SY09100 AS ['Security Roles Master']
INNER JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
INNER JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
GO

GRANT SELECT ON uv_PI_SecurityRolesWithTasks TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Security Views For Use In SmartList Designer: User Access & Granted Security Roles With Tasks

Microsoft Dynamics GPThe third SQL view I’m posting in this series is one which shows the user, the companies to which they have access and the security roles assigned within each company as well as the tasks within the role.

CREATE VIEW [dbo].[uv_PI_UserAccessAndGrantedSecurityRolesWithTasks] 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'
	,ISNULL(['Security Role Task Assignment'].SECURITYTASKID AS 'Security Task ID'
	,ISNULL(['Security Task Master'].SECURITYTASKNAME AS 'Security Task 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
LEFT JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
LEFT JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
GO

GRANT SELECT ON uv_PI_UserAccessAndGrantedSecurityRolesWithTasks TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Security Views For Use In SmartList Designer: User Access & Granted Security Roles

Microsoft Dynamics GPThe second SQL view I’m posting in this series is one which shows the user, the companies to which they have access and the security roles assigned within each company.

CREATE VIEW [dbo].[uv_PI_UserAccessAndGrantedSecurityRolesWithTasks] 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

GRANT SELECT ON uv_PI_UserAccessAndGranted TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Security Views For Use In SmartList Designer: User Access

Microsoft Dynamics GPThe first of the security SQL views I am posting in this series shows all of the users and the companies to which they have been granted access.

CREATE VIEW [dbo].[uv_PI_UserAccessAndGranted] 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'
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	SY01500 AS ['Company Master']
		ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
GO

GRANT SELECT ON uv_PI_UserAccessAndGranted TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Security Views For Use In SmartList Designer: Series Index

Microsoft Dynamics GPBack in November I posted a SQL View which could be used in SmartList Designer to return the users, company and role assignments. This view is now accompanied by several others which allow for reporting on the Dynamics GP security setup at several different levels.

These have been done as SQL views so they can easily be plugged into SmartList Designer which does not have an import/export function, but they can also be used in SmartList Builder, should the client be licensed for this add-on, or any other reporting tool.

Security Views For Use In SmartList Designer
User Access
User Access & Granted Security Roles
User Access & Granted Security Roles With Tasks
Security Roles With Tasks
Populating Security Resource Descriptions
Security Roles With Tasks & Operations

SmartList Builder Will Be Disabled Until It Is Initialized By The System Administrator

Microsoft Dynamics GPI have been involved in several upgrade projects with clients recently where we have been upgrading them from Microsoft Dynamics GP 2010 through to Microsoft Dynamics GP 2013. Since this version was launched, SmartList Builder has been pushed back out to eOne, the original ISV.

So, after doing the normal upgrade process, we have then installed the latest version of SmartList Builder from the eOne website. However, this has not been a smooth process.

One of the clients started receiving the following error message whenever a user tried to access SmartList:

SmartList Builder Will Be Disabled Until It Is Initialized By The System Administrator

Continue reading

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