GP Power Tools Now Available

Winthrop DCWinthrop DC has now officially launched the GP Power Tools and which are available to purchase from Winthrop DC’s partner Mekorma.

The GP Power Tools includes all of the functionality of the old Support Debugging Tool that they replace, but have new functionality, enhancements and bug fixes.

There is a special introductory annual subscription price of US$365 (one free day every four years!).

More information can be found on the GP Power Tools here.

I am/was a Support Debugging Tool user; what now?


If you were a Support Debugging Tool user on Microsoft Dynamics GP 2010 or 2013 then absolutely nothing needs to change. SUpport Debugging Tool will not expire.

That said, you should consider upgrading to GP Power Tools for one major benefit: GP Power Tools is fully supported by Winthrop DC.

While this may not sound like much, the old Support Debugging Tool, which was offered by Microsoft, was not supported at all (not even by Microsoft).

The Future Of The Support Debugging Tool

Winthrop DCI am slightly behind the times with this post as this news is a few weeks old, but I have been busy and am now trying to ctahc up. When David Musgrave was working for Microsoft he wrote the Support Debugging Tool which contained some very useful functions. I did wonder what the future held for it when he left Microsoft last year, but he posted a while ago that he had negotiated an exclusive agreement with Microsoft which allows him to continue work on and release the tool.

There will be some changed to the Support Debugging Tool under this agreement. Most noticeable is the fact that it will now be called GP Power Tools.

GP Power Tools will be initially released for the following Microsoft Dynamics GP versions:

  • v11.0: Microsoft Dynamics GP 2010
  • v12.0: Microsoft Dynamics GP 2013 and GP 2013 R2
  • v14.0: Microsoft Dynamics GP 2015

There is going to be some changes to the functionality when GP Power Tools is launched:

  • New simpler Navigation with menus and area page
  • Database Validation, to ensure that your upgrades work
  • Numerous enhancements and the odd bug fix
  • And lots more….

Another change is that GP Power Tools will now be available via an annual subscription for each customer site at the special introductory price of US$365.00. That’s a dollar a day, and every four years you will get a day for free.

For now continue to use the free Support Debugging Tool for Microsoft Dynamics GP 2010 and GP 2013 (inc. GP 2013 R2) which is available from http://winthropdc.com/SDT.

Stay tuned here or to the WInthrop DC blog for more information on when to upgrade to GP Power Tools for continued support and improved functionality.

Those of you on Microsoft Dynamics GP 2015 will need to be patient for a while longer and wait for the release of GP Power Tools.

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

Microsoft Dynamics GPThe eighth and final view in the series, shows users with their access to companies, roles, tasks and security operations. I’ll state up front that this is probably the least useful fo the views due to the sheer number of rows that it returns.

CREATE VIEW [dbo].[uv_PI_UserAccessAndGrantedSecurityRolesWithTasksAndOperations] 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'
	,ISNULL(['Security Task Master'].SECURITYTASKDESC, '') AS 'Security Task Description'
	,ISNULL(['Security Resource Descriptions'].PRODNAME, '') AS 'Product Name'
	,ISNULL(['Security Resource Descriptions'].Series_Name, '') AS 'Series Name'
	,ISNULL(['Security Resource Descriptions'].DSPLNAME, '') AS 'Security Operation Name'
	,ISNULL(['Security Resource Descriptions'].TYPESTR, '') AS 'Security Operation Type'
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
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
LEFT JOIN
	SY01500 AS ['Company Master']
		ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
LEFT JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
LEFT JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON ['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
LEFT JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON ['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
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: Security Tasks & Operations

Microsoft Dynamics GPThe seventh SQL view in this series does not include users, but instead shows the tasks and 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 Task Master'].SECURITYTASKID AS 'Security Task ID'
	,['Security Task Master'].SECURITYTASKNAME AS 'Security Task Name'
	,['Security Task Master'].SECURITYTASKDESC AS 'Security Task Description'
	,['Security Resource Descriptions'].PRODNAME AS 'Product Name'
	,['Security Resource Descriptions'].Series_Name AS 'Series Name'
	,['Security Resource Descriptions'].DSPLNAME AS 'Security Operation Name'
	,['Security Resource Descriptions'].TYPESTR AS 'Security Operation Type'
FROM
	SY09000 AS ['Security Task Master']
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
GO

GRANT SELECT ON uv_PI_SecurityRolesWithTasksAndOperations TO DYNGRP
GO

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

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'
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
GO

GRANT SELECT ON uv_PI_SecurityRolesWithTasksAndOperations 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_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

GRANT SELECT ON uv_PI_UserAccessAndGrantedSecurityRoles 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