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

One 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 (https://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.