Script to Insert Microsoft Dynamics GP 2018 R2 Missing Security

Microsoft Dynamics GPEach version of Microsoft Dynamics GP which is introduced sees additional functionality introduced; often this additional functionality means new windows are created. Tis in turns means that the security roles and tasks required by Dynamics Gp change.

A fresh install of Dynamics GP includes all of this new functionality by default, but an existing implementation is not updated.

The reason it isn;t automatically updated is to allow the client to decide if the new functionality should be updated or not. To facilitate this, the Dynamics GP Support and Services Blog provides a script for each version with SQL insert statements for the new roles and tasks.

I’ve previously had a post which I updated with this information, but have now created a permanent page linking to the scripts.

I’ll be keeping this page updated in future for all new versions.

MDGP 2018 R2 Feature of the Day: Password Expiration Notification

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 R2 on which I am following and adding commentary. The series index for this series of posts is here.

The twelfth Feature of the Day is a password expiration notification.

This feature is a new notification reminding you that your password will expire in 7 days and prompting you to change it:

Password will expire in 7 days. Do you want to change it now?

I’d be a lot happier if the number of days is configurable as a reminder and reset prompt starting 7 days before expiry is too early a reminder. I have a few clients who have a password policy of the password expiring and needing to be reset every 30 days.

All a prompt 7 days before does is encourage users to change their password when first prompted; this means they change their passwords every three weeks. This massively contributes to password fatigue, leading to the users writing down their password on a post-it note as they don’t, or won’t, remember the password.

A very laudable addition, much beloved by people who write password policies, but, in my experience, the reality on the ground is that this type of policy and early reminder causes more problems than it solves.

Perhaps people would like to share their experience/perspective below? (Due to massive amounts of spam, comments need to be approved before they appear).

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

MDGP 2018 R2 Feature of the Day: Increase Dynamics GP Password Maximum Length

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 R2 on which I am following and adding commentary. The series index for this series of posts is here.

The eleventh Feature of the Day is increase Dynamics GP password maximum length.

This feature see the maximum length of the password usable with Dynamics GP increased from 15 to 21 characters.

Microsoft say that the length of the password in SQL is 21 and this has been matched that so now the maximum password length is the same for Dynamics GP.

User Password Setup

I’m not sure where in SQL the length is 21, as in Dynamics GP 2018 RTM, the password length on Users Master (SY01400) is 15 keyable characters; SQL Server supports password lengths of 128 characters.

While 21 characters is an improvement over 15, it would have been nice to see even longer passwords supported.

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

Implementing Rockton’s SmartFill: Configuring Security

Rockton SoftwareThis post is part of a series of posts on Implementing Rockton’s SmartFill.

As it installs, SmartFill is accessible and the search windows can be used by all users.

It can also be administered by anyone with the POWERUSER* role. However, security can be maintained in two ways.

The first type of security allowsd the lookup windows to be restricted so certain lookup windows can be accessed only by certain users.

To change the security on, for example, the vendor lookup, select SmartFill Objects (Administration area page » Setup » SmartFill » SmartFill Objects). Scroll down and locate the Vendors in the list; select it and click OK:

SmartFill Objects

Continue reading “Implementing Rockton’s SmartFill: Configuring Security”

POWERUSER Removed From ‘sa’; No Other User With Security Access

Microsoft Dynamics GPOne of the project managers was doing some testing for a development project recently and accidentally changed the security for the ‘sa’ account; they removed the ‘POWERUSER’ role (which gives global access to Microsoft Dynamics GP. In its place, they assigned the AP CLERK role.

What made this a major issue, was that the development system only had one company and this left the system with no users with access to the security windows.

They made the mistake by selecting the sa account instead of the sam account. Unfortunately, it was only after logging out as sa and back in as sam that they realised what they had done.

Fortunately, this isn’t actually too complext to fix, although it does require some SQL.

The sa account needed to be added back into the Security Assignment User Role (SY10500) table. I used a very simple script for fixing the development system, but have then tidied it up a little to post here.

This script adds POWERUSER back to the sa account for all companies and needs to be run against the system database (typically called DYNAMICS):

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
INSERT INTO SY10500
	(USERID,CMPANYID,SECURITYROLEID)
--VALUES
	(
	SELECT
		'sa',CMPANYID,'POWERUSER'
	FROM
		SY01500 AS ['Company Master']
	WHERE
		(
		SELECT
			COUNT(*)
		FROM
			SY10500 AS ['Security Assignment User Role']
		WHERE
			['Security Assignment User Role'].CMPANYID = ['Company Master'].CMPANYID
		AND
			['Security Assignment User Role'].USERID = 'sa'
		) = 0
	)
GO

Before running, please make sure you are happy with what the script will do and have a good backup of your system database.

SQL Stored Procedure to Remove Prior Day Logins

Microsoft Dynamics GPIn the last post, I posted a SQL view which returned a list of users who had logged in before the current date. This post contains a SQL stored procedure which will delete any prior day login; this could be scheduled to run using SQL Server Agent:

CREATE PROCEDURE usp_AZRCRV_RemovePriorDayLogins AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
	DELETE FROM
		ACTIVITY
	WHERE
		['User Activity'].LOGINDAT <= DATEADD(DAY, -1, GETDATE())
GO
GRANT EXECUTE ONusp_AZRCRV_RemovePriorDayLogins TO DYNGRP
GO

Before using this script on a live system, I’d recommend testing it on a standalone test system first.

SQL View to Return Prior Day Logins

Microsoft Dynamics GPMicrosoft Dynamics GP is licensed, for full users, on a concurrent user basis. This means that you can create more users than can be logged in at the same time; unfortunately, this means that if users don;t log out correctly, that the license remains in use.

The below script can be plugged into a SmartList Designer to give easy visibility of who logged in before the current day.

CREATE VIEW uv_AZRCRV_GetPriorDayLogins AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	['User Activity'].USERID AS 'User ID'
	,ISNULL(['Users Master'].USERNAME, 'User Master record not found') AS 'User Name'
	,ISNULL(['Company Master'].INTERID, 'Company Master record not found') AS 'Inter ID'
	,['User Activity'].CMPNYNAM AS 'Company Name'
	,FORMAT(['User Activity'].LOGINDAT, 'yyyy-MM-dd') AS 'Login Date'
	,FORMAT(['User Activity'].LOGINTIM, 'hh:mm:ss') AS 'Login Time'
FROM
	ACTIVITY AS ['User Activity']
LEFT JOIN
	SY01400 AS ['Users Master']
		ON
			['User Activity'].USERID = ['Users Master'].USERID
LEFT JOIN
	SY01500 AS ['Company Master']
		ON
			['User Activity'].CMPNYNAM = ['Company Master'].CMPNYNAM
WHERE
	['User Activity'].LOGINDAT <= DATEADD(DAY, -1, GETDATE())
GO
GRANT SELECT ON uv_AZRCRV_GetPriorDayLogins TO DYNGRP
GO

Hands On With MDGP 2018 RTM New Features: SmartList Favorite Password Protection

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The eighteenth feature of the day is SmartList Favourite Password Protection.

In Microsoft Dynamics GP 2016 R2, SmartList Favourite Protection was introduced; however, this protection was via a global password for all SmartList Favourites.

In this 2018 new feature, Microsoft have added the ability to set a separate password for each favourite.

To do this, click the Favorites button. Enter a Name for the SmartList and enter the required password in the Password field.

Click Add:

Add or Remove Favorites

Continue reading “Hands On With MDGP 2018 RTM New Features: SmartList Favorite Password Protection”

Hands On With MDGP 2018 RTM New Features: Copy User Access Across AA Dimensions

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The seventeenth feature of the day is Copy User Access Across AA Dimensions.

Analytical Accounting is one of those modules which I, and a lot of other consultants, will try to guide clients away from. It is overly complex and time consuming to setup, use and, most certainly, to report from.

This new feature should, somewhat, simplify the configuration of access to alphanumeric dimensions by allowing the granted access to be copied between users.

To do this, open the User Access to Trx Dimension Codes window (Administration area page >> Setup >> Company >> Analytical Accounting >> User Access) and click the Copy button:

User Access to Trx Dimension Codes

Continue reading “Hands On With MDGP 2018 RTM New Features: Copy User Access Across AA Dimensions”

Hands On With MDGP 2018 RTM New Features: System Password Remembered

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The fifteenth feature of the day, is Remember System Password.

The new feature means that the system password now only needs to be entered once per session:

Enter the system password.

If you have ever needed to build a new security model for Dynamics GP, you will have become heartily sick of having to enter the system password multiple times. In fact, every single time a protected window was opened, the password needed to be entered.

This feature is a most welcome one, but has one drawback. Once entered, the system password is remembered for the rest of the session. This means if you enter it for a user, such as, for example, to give them access to the VAT Return window, they can then access any other window which is usually protected with the system password.

This shouldn’t generally be a problem as the security roles would have locked down the security windows so normal users wouldn’t see them, but it is something to be aware of and consider.

Click to show/hide the Hands On With MDGP 2018 RTM New Features Series Index