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) |
-- 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;
Until next post!
MG.-