SQL: Assigning Microsoft Dynamics GP Users to SSRS Database Roles

As I begin to wrap up a Microsoft Dynamics GP 2010 R2 production upgrade from Microsoft Dynamics GP 9.0, I ran into a small issue at my client. After deploying the new SSRS reports, and as users were getting ready to try them out, we realized that some 15 logins needed to be assigned to a number of the 24 default database security roles created for the SSRS reports.

User Mappings (some information blurred to protect the client's identity)

This would be a bit cumbersome giving the share number of clicks required to accomplish this feat. In addition, we had just setup Microsoft Dynamics GP security, and given that the SSRS database roles were similar to those in GP, something needed to be done to automate the assignment of these roles based on Microsoft Dynamics GP security roles.

As a result, I created the following script:

-- Created by Mariano Gomez, MVP
--  This code is licensed under the Creative Commons 
--  Attribution-NonCommercial-ShareAlike 2.5 Generic license.
use DYNAMICS;
go

DECLARE @userid varchar(50), @companyid varchar(5), @securityroleid varchar(200), @ssrsRole varchar(200);
DECLARE @sqlStmt varchar(255);

DECLARE c_reportsecurity CURSOR FOR 
	SELECT a.USERID, b.INTERID, a.SECURITYROLEID FROM SY10500 a
		LEFT OUTER JOIN SY01500 b ON (A.CMPANYID = b.CMPANYID)
	WHERE a.USERID not in ('sa', 'DYNSA', 'LESSONUSER1', 'LESSONUSER2') and a.SECURITYROLEID NOT LIKE ('MBS%')
	ORDER BY a.USERID;

OPEN c_reportsecurity;
FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @ssrsrole = 
		CASE 
			WHEN @securityroleid = 'ACCOUNTING MANAGER*      ' THEN 'rpt_accounting manager'
			WHEN @securityroleid = 'AP CLERK*                ' THEN 'rpt_accounts payable coordinator'
			WHEN @securityroleid = 'AR CLERK*                ' THEN 'rpt_accounts receivable coordinator'
			WHEN @securityroleid = 'BOOKKEEPER*              ' THEN 'rpt_bookkeeper'
			WHEN @securityroleid = 'CA AGENT*                ' THEN ''
			WHEN @securityroleid = 'CA MANAGER*              ' THEN ''
			WHEN @securityroleid = 'CA STAKEHOLDER*          ' THEN ''
			WHEN @securityroleid = 'CERTIFIED ACCOUNTANT*    ' THEN 'rpt_certified accountant'
			WHEN @securityroleid = 'CL AGENT*                ' THEN ''
			WHEN @securityroleid = 'CL DISPATCHER*           ' THEN 'rpt_dispatcher'
			WHEN @securityroleid = 'CL MANAGER*              ' THEN ''
			WHEN @securityroleid = 'CL STAKEHOLDER*          ' THEN ''
			WHEN @securityroleid = 'CUSTOMER SERVICE REP*    ' THEN 'rpt_customer service rep'
			WHEN @securityroleid = 'DP MANAGER*              ' THEN ''
			WHEN @securityroleid = 'DP STAKEHOLDER*          ' THEN ''
			WHEN @securityroleid = 'DP TECHNICIAN*           ' THEN ''
			WHEN @securityroleid = 'FA MANAGER*              ' THEN 'rpt_accounting manager'
			WHEN @securityroleid = 'FA STAKEHOLDER*          ' THEN 'rpt_certified accountant'
			WHEN @securityroleid = 'IT OPERATIONS MANAGER*   ' THEN ''
			WHEN @securityroleid = 'MBS DEBUGGER ADMIN       ' THEN ''
			WHEN @securityroleid = 'MBS DEBUGGER USER        ' THEN ''
			WHEN @securityroleid = 'OPERATIONS MANAGER*      ' THEN 'rpt_operations manager'
			WHEN @securityroleid = 'ORDER PROCESSOR*         ' THEN 'rpt_order processor'
			WHEN @securityroleid = 'PAYROLL CLERK*           ' THEN 'rpt_payroll'
			WHEN @securityroleid = 'PM AGENT*                ' THEN ''
			WHEN @securityroleid = 'PM MANAGER*              ' THEN ''
			WHEN @securityroleid = 'PM STAKEHOLDER*          ' THEN ''
			WHEN @securityroleid = 'POWERUSER                ' THEN 'rpt_power user'
			WHEN @securityroleid = 'PURCHASING AGENT*        ' THEN 'rpt_purchasing agent'
			WHEN @securityroleid = 'PURCHASING MANAGER*      ' THEN 'rpt_purchasing manager'
			WHEN @securityroleid = 'RT AGENT*                ' THEN ''
			WHEN @securityroleid = 'RT MANAGER*              ' THEN ''
			WHEN @securityroleid = 'RT STAKEHOLDER*          ' THEN ''
			WHEN @securityroleid = 'SHIPPING AND RECEIVING*  ' THEN 'rpt_shipping and receiving'
			WHEN @securityroleid = 'WAREHOUSE MANAGER*       ' THEN 'rpt_warehouse manager'
			WHEN @securityroleid = 'WENNSOFT SMS CONTRACTS*  ' THEN ''
			WHEN @securityroleid = 'WENNSOFT SMS DISPATCHER* ' THEN ''
			WHEN @securityroleid = 'WENNSOFT SMS POWER USER* ' THEN ''
			WHEN @securityroleid = 'WENNSOFT SMS SETUP*      ' THEN ''
			WHEN @securityroleid = 'WSJC ACCOUNTANT*         ' THEN ''
			WHEN @securityroleid = 'WSJC ACCOUNTING MANAGER* ' THEN ''
			WHEN @securityroleid = 'WSJC ADMIN*              ' THEN ''
			WHEN @securityroleid = 'WSJC BILLING CLERK*      ' THEN ''
			WHEN @securityroleid = 'WSJC POWERUSER*          ' THEN ''
			WHEN @securityroleid = 'WSJC PROJECT MANAGER*    ' THEN ''
			WHEN @securityroleid = 'WSTT PAYROLL CLERK*      ' THEN ''
			WHEN @securityroleid = 'WSTT POWERUSER*          ' THEN ''
		END
	
	IF (@ssrsRole <> '') 
	BEGIN
		SET @sqlStmt = 'USE ' + rtrim(@companyid) + '; EXEC sp_addrolemember ' + QUOTENAME(@ssrsRole, '''') + ',' + QUOTENAME(rtrim(@userid), '''');
		EXEC(@sqlStmt);
	END
	FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;
END

CLOSE c_reportsecurity;
DEALLOCATE c_reportsecurity;

The script looks at the Security Assignment User Role table (SY10500) and retrieves the physical company database from the Company Master table (SY01500), then assign an SSRS database security role to each of the Microsoft Dynamics GP default roles. If a role does not exist, you can choose to leave the assignment blank.

The script then proceeds to evaluate the database security role obtained, then creates a SQL string that can be executed. The SQL string uses the sp_addrolemember system stored procedure to add the corresponding SQL login to the role. A cursor is used to loop through each user, company, and security role combination to obtain and assign the proper SSRS database role.

You can choose to add custom security roles or roles for third party applications that deploy their own SSRS reports to the above script.

This definitely helped saving some time... phew!

Until next post!

MG.-

Mariano Gomez, MVP

IntellPartners, LLC

http://www.IntellPartners.com/