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 DYNAMCIS:

/*
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

Hands On With MDGP 2018 RTM New Features: DocAttach Security Setup

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.

I am going to do this feature of the day a little out of order. The third Feature of the Day post from Microsoft was Doc Attach Security Setup for Attachments on (In|En)quiry windows.

Two new fields have been added to the Document Attachment Setup window (Administration area page >> Setup >> Company >> Document Attachment Setup):

Document Attachment Setup

I said in the Feature of the Day Post on this feature, that I couldn’t really see a use case for the additional security. In the, almost, three months since, I still haven’t come up with a scenario where I think the security is useful.

For those clients using DocAttach, they want users to be able to attach documents to any record; there is no requirements for security around the enquiry windows, and certainly not to the extent of requiring a password.

For setup purposes, there is another checkbox which will need to be marked when enabling the feature, but little more impact, so it’s not like the security is onerous.

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

Problems Logging into Microsoft Dynamics GP as sa After Password Change

Microsoft Dynamics GPI’ve been working with a client recently on an upgrade to Microsoft Dynamics GP 2018 and all has gone well overall. However, they had a security project running at the same time, where they were changing the passwords for a number of accounts including the SQL Srver sa account.

Unfortunately, the password length they were setting was 20 characters,. They supplied the new password to me, but I found I couldn’t log in. When I then tried to copy and paste the password into the field, nothing happened (which is what clued me into the issue).

The cause of this login issue, is that the Password field on the Users Master (SY01400) table has a maximum Keyable Length of 15 characters, and this restriction is enforced on all user ids entered on the login window.

So for me to complete the final steps of the upgrade, the password for the sa account was changed to a 15 character one; after the upgrade was complete, the password was again changed to a longer one and then the account disabled.

Best practice is to use the sa account to administer only SQL Server and to use either a named user account in Dynamics GP or to use the DYNSA account. I am encouraging all clients to ensure this is the case.

There is a wide range of ways you can better secure your system. One way of working through these is to engage with your partner and another is to purchase the Microsoft Dynamics GP Security and Audit Field Manual: Dynamics GP 2016 book by MVP Mark Polino and Andy Snook. This book is for the 2016 version of Dynamics GP, but still fully applies to Microsoft Dynamics GP 2018 (as well as earlier versions).

Microsoft Dynamics GP User? Then Review Your Security

Microsoft Dynamics GPIn recent times we have taken over a few clients from other partners and one common theme we are running into is poor security practices. For a lot of these new clients, we find that a lot of people know the sa password, the vast majority know the System Password and all users have the POWERUSER* role assigned.

All of these are bad:

  1. sa is the SQL Server system administrator account and should only be used for the initial implementation of Dynamics GP, when Dynamics GP is moved between servers, or when configuring some ISV products.

    When Dynamics GP is implemented, an account called DYNSA is created; this is the Dynamics System Administrator account which is intended for use by the people/team administering Microsoft Dynamics GP.

    However, most of the admin is actually creating or maintaining users and this can be done with any user account, once it has been configured with the relevant permissions.

  2. The System Password should only be known to those people who have a need to know it. It protects access to some windows in the system which can cause problems if used incorrectly.
  3. POWERUSER* is a role which gives access to all windows in Microsoft Dynamics GP; combined with users knowing the System Password can be a recipe for disaster. New security roles should be created and assigned to users which give them access to the windows they need.

Even when users have security roles rather than POWERUSER*, I still recommend clients review the access rights to ensure that people have access they need; this is true for all users. I recommend everyone review their security every year or two to ensure that the changing needs of the business are being met.

We do a fair bit of work for clients assisting them in reviewing the security, but not everyone has a partner they can work with on this type of project. And not everyone has the budget for a full-scale security review with their partner.

One other option, to involving your partner, is to pick up a copy of the Microsoft Dynamics GP Security and Audit Field Manual: Dynamics GP 2016 book by MVP Mark Polino and Andy Snook. This book is for the 2016 version of Dynamics GP, but still fully applies to Microsoft Dynamics GP 2018 (as well as earlier versions).

Microsoft Dynamics GP 2018 RTM Web Client Prerequisites: Bind SSL Certificate to IIS Web Site

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released. In a series of posts, I am be stepping through the installation of Microsoft Dynamics GP and additional products ; the series index for this series of posts is here and will automatically update as posts go live.

I am taking a small break (sort of) from that post, to cover the installation/configuration of the prerequisites for the installation of the Microsoft Dynamics GP 2018 RTM web client (the installation of which is part of the Hands On series.

This is the first of the posts on installing and configuring the web client prerequisites; in this post I am going to cover the binding of the self-signed security certificate (created in the last post).

To bind the certificate, launch Internet Information Services (IIS) Manager, expand the server node, the Sites node and click on Default Web Site (I am just using the default website in IIS for the web client) and then click Bindings in the Action pane:

Internet Information Services (IIS) Manager

Continue reading “Microsoft Dynamics GP 2018 RTM Web Client Prerequisites: Bind SSL Certificate to IIS Web Site”