Dynamics GP - Jordan Jimenez

Grant Script


For Dynamics GP, when an object is added to the company database, this script can be used to assign all users to have access to the database object with DYNGRP permissions. This is primarily intended to be used in Microsoft SQL Server Management Studio. 



​------------------------------------------------------------
-- SQL SCRIPT TO GRANT ALL USERS UNDER DYNGRP GROUP 
-- ACCESS PRIVILEDGES TO ALL TABLES AND STORED PROCEDURES
-- RUN THIS SCRIPT UNDER A SELECTED COMPANY DB IN DYNAMICS.
------------------------------------------------------------

DECLARE @SQL_CMD VARCHAR(255)
DECLARE SQL_CURSOR CURSOR 
FOR SELECT 'GRANT SELECT,UPDATE,INSERT,DELETE 
ON [' + CONVERT(VARCHAR(64),NAME) + '] TO DYNGRP' 
FROM SYSOBJECTS 
WHERE (TYPE = 'U' OR TYPE = 'V') AND UID = 1

SET NOCOUNT ON
OPEN SQL_CURSOR
FETCH NEXT FROM SQL_CURSOR INTO @SQL_CMD 
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    EXEC (@SQL_CMD)
    FETCH NEXT FROM SQL_CURSOR INTO @SQL_CMD 
END
DEALLOCATE SQL_CURSOR

DECLARE SQL_CURSOR CURSOR 
FOR SELECT 'GRANT EXECUTE ON [' + CONVERT(VARCHAR(64),NAME) + '] TO DYNGRP' 
FROM SYSOBJECTS 
WHERE TYPE = 'P'  

SET NOCOUNT ON
OPEN SQL_CURSOR
FETCH NEXT FROM SQL_CURSOR INTO @SQL_CMD 
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    EXEC (@SQL_CMD)
    FETCH NEXT FROM SQL_CURSOR INTO @SQL_CMD 
END
DEALLOCATE SQL_CURSOR