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

MDGP 2015 R2 Feature of the Day: Self Service User Tasks and Roles

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The twelfth Feature of the Day is Self Service User Tasks and Roles.

In the Microsoft Dynamics GP 2015 R2 release a Self Service user type was added; this user type will enable users that only require very limited access to the system a less expensive option to perform tasks such as entering payroll time, entering project time and expenses, or creating a requisition.

This feature also includes Employee Self Service Security Roles and the ability to filter on security tasks available for each user type, which will make the task and role assignment more efficient.

Security Role Setup & Security Role Setup

Nice to see that the new user type is also getting some easing of the setup of the roles and tasks. This is one area that, despite the default roles and tasks available, seems to daunt a lot of new users to the system.

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

MDGP 2015 R2 Feature of the Day: Self Service User Type

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The eleventh Feature of the Day is Self Service User Type.

Microsoft Dynamics GP 2015 R2 sees a Self Service user type added.

This user type will enable users that only require very limited access to the system a less expensive option to perform tasks such as entering payroll time, entering project time and expenses, or creating a requisition.

User Setup

This new user type is going to be a really useful addition. I have one client who has been looking for a POP add-on to allow online creation of orders for more than 400 users and all the old options were looking quite pricey.

This Self Service user type used in conjunction with the web client should give them the functionality they want, at a price point they can accept.

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

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

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
Security Tasks & Operations
User Access & Granted Security Roles With Tasks & Operations

Scripts to Reset System and Budget Passwords

Microsoft Dynamics GPIf a user forgets the password set against a Budget it cannot be amended through Microsoft Dynamics GP, but can be removed through SQL Server; the same stands true for the System Password as well.

The first script in this post, removes the password of a budget; this SQL is not mine, but was posted by Leslie Vail in this post on the Dynamics Community forum.

To run this script change the highlighted section to the name of the budget which needs the password reset:

UPDATE
	GL00200
SET
	BUDPWRD = 0x00202020202020202020202020202020
WHERE
	BUDGETID = 'budget id'
GO

A very similar script can be used to reset the System Password. If you are using a Named System Database, change the highlighted DYNAMICS text to the name of your System Database:

USE DYNAMICS
GO
UPDATE
	SY02400
SET
	Password = 0X00202020202020202020202020202020
GO