Monday, August 8, 2011

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

4 comments:

Beat BUCHER said...

Hi Mariano,
That was exactly the response that I never got from MS when asked in Fargo during the GP Tech Conference... It seems that there is a gap that MS forgot to fill between the GP security and the SSRS report security... Yes they create all those SQL roles when the wizard installs the GP reports, but the security does not get assigned at all... Thanks for that script that I'll use to adjust accordingly to my GP security roles :-).
Have a great time.
Beat

Anonymous said...

There is something I do not understand. The script assigns the sql user associated to the GP Userid to the role but GP SSRS reports are set up to work with windows authentication as the default. So, assigning the role to the GP sql user doesn't have any effect with the GP user lunches the report. am I missing something?

Mariano Gomez said...

Maybe I should have clarified. My customer's GP users match their domain users and I guess what I meant to indicate was that you would have to go through that same process of matching the user IDs too if they were different. But that's a lot easier if you create a simple table that correlates the domain user with the GP user ID.

MG.-

ron.mcvicar said...

Mariano,
What a great post (as usual) and thanks. Now that it has been a while any update or future thoughts since the post?
I want to start migrating my company's SSRS reports for Dynamics GP to GP Security so certain user can only access certain reports / data based upon their Dynamics GP 2010 Role.