#GPPT Running SQL Scripts for All Companies, such as a smarter GRANT script

This article has two purposes. The first is to highlight a very useful script published by Michael Krasivsky from The Resource Group back in 2016. The second is to explain how easy it is to use the GP Power Tools SQL Execute Setup window to execute scripts against multiple databases.

Microsoft Dynamics GP provides access to all the SQL Resources (Tables, Views, Stored Procedures, etc.) using the DYNGRP SQL role. For this technique to work correctly, all GP users need to be assigned to the DYNGRP role and all SQL objects need access “Granted” to the same DYNGRP role.

The granting of access to all the SQL objects happens during the installation of Microsoft Dynamics GP, but sometimes there are objects added by end customers, consultants or developers and they don’t always remember to grant access.

This will cause issues where the application or customization works perfectly for the ‘sa’ user (who inherently has access to everything) and then falls over with access errors for any other user.

The fix that everyone calls out when this occurs is to run the GRANT.SQL script which can be found in the SQL\Util folder underneath the Application folder that GP is installed in. However, this script is very inefficient as it will grant access to all objects in a database whether they already have access or not.

So, this is where Michael’s smart version of the script comes into play. It creates a list of the objects that need access granted and then only grants access to those objects. Much smarter and more efficient.

I will include the script here for reference (in case the original article goes offline):

-- Script by By Michael Krasivsky, The Resource Group
-- http://www.erpsoftwareblog.com/2016/08/create-efficient-grant-sql-script-dynamics-gp/

DECLARE @cStatement VARCHAR(max)

SELECT o.*
INTO #temp
FROM (
	SELECT s.NAME
		,s.id
		,t.*
	FROM sysobjects s
	FULL JOIN (
		SELECT 'SL'
			,'select'
		
		UNION ALL
		
		SELECT 'UP'
			,'update'
		
		UNION ALL
		
		SELECT 'IN'
			,'insert'
		
		UNION ALL
		
		SELECT 'DL'
			,'delete'
		) t(type, PERM) ON 1 = 1
	WHERE (
			s.type = 'U'
			OR s.type = 'V'
			)
		AND s.uid = 1
	
	UNION ALL
	
	SELECT s.NAME
		,s.id
		,t.*
	FROM sysobjects s
	FULL JOIN (
		SELECT 'EX'
			,'execute'
		) t(type, PERM) ON 1 = 1
	WHERE s.type = 'P'
	) o
LEFT JOIN (
	SELECT major_id
		,type
	FROM sys.database_permissions
	) p ON o.id = p.major_id
	AND o.type COLLATE DATABASE_DEFAULT = p.type COLLATE DATABASE_DEFAULT
WHERE p.major_id IS NULL

DECLARE G_cursor CURSOR
FOR
SELECT 'grant' + STUFF((
			SELECT ', ' + t.PERM
			FROM #temp t
			WHERE t.id = o.id
			FOR XML PATH('')
			), 1, 1, '') + ' on [' + NAME + '] to DYNGRP'
FROM #temp o
GROUP BY NAME
	,id

SET NOCOUNT ON

OPEN G_cursor

FETCH NEXT
FROM G_cursor
INTO @cStatement

WHILE (@@FETCH_STATUS <> - 1)
BEGIN
	EXEC (@cStatement)

	FETCH NEXT
	FROM G_cursor
	INTO @cStatement
END

DEALLOCATE G_cursor

DROP TABLE #temp

The next question then is “How can I quickly execute this smarter GRANT script?”.

One simple answer is to use GP Power Tools and the SQL Execute Setup window with the Execute Script for all Companies checkbox. You can also open an expansion window and adjust which companies/databases to run the script for, this allows you to add the System database to the list.

Here is the exported configuration setup file which can be unzipped and imported into GP Power Tools using the Configuration Export/Import window:

• GP Power Tools Settings Grant.dbg.zip

Feel free to download and install in your system and execute it against all databases. It is very quick if there is nothing to do.

More Information

Here are links to Michael’s original article and other related articles:

• How to Create a More Efficient Grant.sql Script for Dynamics GP
• Running a SQL Script against all GP Company Databases
• Running SQL commands against all GP Company Databases

Hope you find this information helpful.

Regards

David

This article was originally posted on http://www.winthropdc.com/blog.