CRM 2011 - Windows Azure Installation Error

I went to install CRM 2011 for a customer last Friday on a brand new server only to get stuck at the Pre-Req screen with a "fatal error during installation" result for the Windows Azure platform AppFabric SDK 1.0.

I did a repair on .NET 4.0 and even did a full uninstall and re-install only to get the same result. I downloaded the WindowsAzureAppFabricSDK-x64.msi file directly from Microsoft's page (April Update) only to get a similar error. I looked in the Event Viewer and saw the following error:

Error 1026, .NET Runtime
Application: RelayConfigurationInstaller.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception
Exception Info: exception code e0434352, exception address 000007FEFCF2AA7D

It turns out it is a known issue that the AppFabric team is working on. See this thread from the MSDN forums.

To get past this error, you can either:

1) Download the previous build from here:
https://mercuryit.blob.core.windows.net/$root/WindowsAzureAppFabricSDK-x64.msi
You can also find this link in the thread in the MSDN forums.
Whenever they come up with a fix it is suggested to download and upgrade to the newer build.

2) Download v 2.0 of the AppFabric from here:
http://www.microsoft.com/download/en/details.aspx?id=17691
I have not personally tried this one, but see this thread for success stories.

Upgrading GP v9 Alt/Mod Reports to v10 or 2010

The upgrade from version 9 to version 10 asks, in so many words, if you want to transfer and upgrade the GP security from the old, not-so-great way of doing things to the new and much improved version. "No" is the appropriate answer there, but the drawback is that the settings for which modified reports are used are not pulled over, either. This can be a huge headache if there are a lot of modified reports as every user could have a completely different setting in each company; on top of that not all of the modified reports may even be used. A lot of times this is not by design - one user needed a report to be customized and thus is the only one with permission to the custom report, or someone was playing with the idea of changing a report, pulled it over in Report Writer to do so, but never actually made the change. Voila! You now have a long list of modified reports that aren't necessarily modified, and the permissions to the ones that actually are modified are not consistent.

I should created something to help with this a long time ago when we actually had a bunch of v9 upgrades left to do, but I recently ran into a situation where it was worth creating just for one customer.

Two things to note about this script: 1) it will only work with Modified Reports, and 2) you have to make a decision about how you want to pull in these permissions. You can have it just set up a single Alt/Mod Form/Report ID to where if anyone had permission in version 9 that everyone now has permission in version 10/2010 or you can break it up into how many times that report is given permission. For example, if ten users have permissions to report 1, five users have permissions to report 2, and eight users have permissions to report 3, you might create two Alt/Mod Form/Report IDs: one for six to ten users and another for one to five users. Run the script multiple times in the order of most min/max average users to least min/max average users, as in the previous example.

Even if you have to do some manual tweaking afterwards, you are much farther ahead than if you did not run it at all. Depending on your particular case, just running the query at the beginning comment section might be enough to help you manually create the settings.

If you do not like the result of any run of the script, you can simply delete the new Alt/Mod Form/Report ID from inside Dynamics GP.

/*
Restore the v9 DYNAMICS database to a new database called DYNAMICSv9
Populate the SY09400 table in the v10 DYNAMICS database to see the names of the reports
  Inside Dynamics GP, go to Maintenance >> Clear Data
  Click Display >> Physical Data
  Change Series to System
  Select Security Resource Descriptions, click Insert, OK and Yes
  Even though you chose Clear Data, it is actually adding it in
  Everyone else has to be out of GP to do the Clear Data or it will not run

SELECT
 CMPANYID,DSPLNAME,RESID, count(DISTINCT V9.USERID) C
FROM 
 DYNAMICSv9..SY02000 V9
LEFT JOIN DYNAMICS..SY09400 Descr
 ON V9.resid = Descr.SECURITYID
 AND V9.restype = Descr.SECRESTYPE
 AND V9.dictid = Descr.DICTID
WHERE 
 V9.DICTID = 0
 AND RESTYPE = 23
GROUP BY
 CMPANYID,DSPLNAME,RESID
ORDER BY
 CMPANYID,RESID


If you run this script and do not like the results, simply delete the created
  Alt/Modified Forms/Reports ID from inside Dynamics GP or manually update it

*/

-- SETTING UP VARIABLES

DECLARE @ALTMODFRMREP_ID CHAR(25)
DECLARE @ALTMODFRMREP_DESCRIPTION CHAR(50)
DECLARE @CREATEDATE DATETIME
DECLARE @USERID CHAR(15)
DECLARE @MAXAVERAGE INT
DECLARE @MINAVERAGE INT


-- CHANGE THESE VARIABLES AS NEEDED
SET @ALTMODFRMREP_ID =            'DEFAULTUSERYC'                -- ID: THIS MUST BE ALL CAPS (15 CHARS)
SET @ALTMODFRMREP_DESCRIPTION =    'Default YourCompany User'    -- DESCRIPTION: DOES NOT HAVE TO BE CAPS (50 CHARS)
SET @CREATEDATE =                '6/9/2011'                    -- USE TODAY'S DATE
SET @USERID =                    'sa'                        -- CAN LEAVE 'sa'

                                                            -- RUN THE SQL QUERY FOUND IN THE COMMENTS ABOVE TO SEE THE 
                                                            --     AVERAGES TO USE IN THE NEXT TWO VARIABLES
                                                            --   THESE WILL DEPEND ON HOW MANY COMPANIES YOU HAVE
                                                            --   OR HOW MANY SETS OF ALT/MOD SETTINGS THERE ARE.
                                                            --   SORRY - NO SCRIPT TO HELP YOU MAKE THAT DECISION
SET @MINAVERAGE =                1                            -- MIN NUMBER OF AVERAGE OCCURRENCES OF USERS USING THE REPORT IN V9
SET @MAXAVERAGE =                1000                            -- MAX NUMBER OF AVERAGE OCCURRENCES OF USERS USING THE REPORT IN V9


-- CREATING THE ALT/MOD FORM/REPORT ID
INSERT INTO SY09200 (SECMODALTID,SECMODALTDESC,CRUSRID,CREATDDT,MDFUSRID,MODIFDT)
VALUES (@ALTMODFRMREP_ID,@ALTMODFRMREP_DESCRIPTION,@USERID,@CREATEDATE,@USERID,@CREATEDATE)

-- COPYING THE SMARTLISTS, ETC. FROM DEFAULTUSER
INSERT INTO SY10800 (SECMODALTID,DICTID,SECURITYID,SECRESTYPE,ALTDICID,Modified)
    SELECT @ALTMODFRMREP_ID,DICTID,SECURITYID,SECRESTYPE,ALTDICID,Modified
    FROM SY10800 WHERE SECMODALTID = 'DEFAULTUSER'

-- UPDATING THE REPORTS ALREADY MARKED AS MODIFIED OR ALTERNATE AS THE V9 MODIFIED OR ALTERNATE REPORTS
UPDATE SY10800
    SET ALTDICID = V9.ALTDICID,
        Modified = 1
    FROM SY10800 V10 INNER JOIN 
        (SELECT DICTID,RESID,RESTYPE,ALTDICID
        FROM DYNAMICSv9..SY02000 V9                            -- v9 database
        WHERE DICTID = 0 AND RESTYPE = 23
            AND EXISTS (
            SELECT DICTID,SECURITYID,SECRESTYPE FROM SY10800 WHERE SECMODALTID = @ALTMODFRMREP_ID
                AND SY10800.SECRESTYPE = V9.RESTYPE AND SY10800.SECURITYID = V9.RESID AND SY10800.DICTID = V9.DICTID
        )
        GROUP BY DICTID,RESID,RESTYPE,ALTDICID
        HAVING COUNT(distinct USERID) BETWEEN @MINAVERAGE AND @MAXAVERAGE
        ) V9 ON V10.DICTID = V9.DICTID AND V10.SECRESTYPE = V9.RESTYPE AND V10.SECURITYID = V9.RESID
    WHERE V10.SECMODALTID = @ALTMODFRMREP_ID

-- ADDING THE REPORTS FROM V9
INSERT INTO SY10800 (SECMODALTID,DICTID,SECURITYID,SECRESTYPE,ALTDICID,Modified)
    SELECT @ALTMODFRMREP_ID,DICTID,RESID,RESTYPE,ALTDICID,1
    FROM DYNAMICSv9..SY02000 V9                                -- v9 database
    WHERE DICTID = 0 AND RESTYPE = 23
        AND NOT EXISTS (
        SELECT DICTID,SECURITYID,SECRESTYPE FROM SY10800 WHERE SECMODALTID = @ALTMODFRMREP_ID
            AND SY10800.SECRESTYPE = V9.RESTYPE AND SY10800.SECURITYID = V9.RESID AND SY10800.DICTID = V9.DICTID
    )
    GROUP BY DICTID,RESID,RESTYPE,ALTDICID
    HAVING COUNT(distinct USERID) BETWEEN @MINAVERAGE AND @MAXAVERAGE

-- SETTING UP THE USERS TO USE THE REPORTS - THESE PARTS ARE OPTIONAL
--   UPDATING EXISTING PERMISSIONS
UPDATE SY10550
    SET SECMODALTID = @ALTMODFRMREP_ID
    FROM DYNAMICSv9..SY02000 V9                                -- v9 database
    INNER JOIN SY10550 V10 ON V9.USERID = V10.USERID AND V9.CMPANYID = V10.CMPANYID
    WHERE EXISTS (
        SELECT DICTID,RESID,RESTYPE
            FROM DYNAMICSv9..SY02000 V9b                    -- v9 database
            WHERE DICTID = 0 AND RESTYPE = 23
                AND V9.DICTID = V9b.DICTID
                AND V9.RESTYPE = V9b.RESTYPE
                AND V9.RESID = V9b.RESID
            GROUP BY DICTID,RESID,RESTYPE
            HAVING COUNT(distinct USERID) BETWEEN @MINAVERAGE AND @MAXAVERAGE 
        )
    AND EXISTS (
        SELECT USERID,CMPANYID
        FROM SY10550 V10
        WHERE V9.USERID = V10.USERID
            AND V9.CMPANYID = V10.CMPANYID
        )
        
--   ADDING NEW ONES IF APPLICABLE
INSERT INTO SY10550 (USERID,CMPANYID,SECMODALTID)
    SELECT USERID,CMPANYID,@ALTMODFRMREP_ID
    FROM DYNAMICSv9..SY02000 V9                                -- v9 database
    WHERE EXISTS (
        SELECT DICTID,RESID,RESTYPE
            FROM DYNAMICSv9..SY02000 V9b                    -- v9 database
            WHERE DICTID = 0 AND RESTYPE = 23
                AND V9.DICTID = V9b.DICTID
                AND V9.RESTYPE = V9b.RESTYPE
                AND V9.RESID = V9b.RESID
            GROUP BY DICTID,RESID,RESTYPE
            HAVING COUNT(distinct USERID) BETWEEN @MINAVERAGE AND @MAXAVERAGE 
        )
    AND NOT EXISTS (
        SELECT USERID,CMPANYID
        FROM SY10550 V10
        WHERE V9.USERID = V10.USERID
            AND V9.CMPANYID = V10.CMPANYID
        )
    GROUP BY USERID,CMPANYID



/*
-- SEVERAL LOOKUPS TO HELP FIND THE DATA - ALL OF THESE ARE IN THE DYNAMICS DATABASE
select * from DYNAMICSv9..SY02000 -- THIS IS IN THE V9 DYNAMICS DATABASE
select * from sy10800
SELECT * FROM SY09200
SELECT * FROM SY10550
SELECT INTERID,CMPNYNAM,CMPANYID FROM SY01500
*/

'sa', DYNSA, and POWERUSER in Dynamics GP

Updated 4/2/2015

I sometimes get questions about whether the 'sa' user is required for Microsoft Dynamics GP, what an alternative for 'sa' is, and what the POWERUSER Security Role really does. Well, I'm not a Dynamics GP developer at Microsoft, but here is what I know from what I've seen. Keep in mind this is not a How-To for setting up GP security.

Before going into the differences of each type of user or role, let me explain a little about GP Security. 

During the initial installation and each subsequent company database creation, a database role called DYNGRP is created in each database and assigned permissions to most objects in each database (Tables, Views, and Stored Procedures). This one database role is the basis for all SQL database access for GP users - with the exception of the 'sa' user, but more on that later.

When you create a new user in GP, the following key actions take place, not necessarily in this order and not limited to this list:

• A SQL login is created
• A GP user is created in DYNAMICS..SY01400
• The user is assigned to the DYNGRP database role in the DYNAMICS database
• The user is assigned to the DYNGRP database role in each respective company given to it in the User Access window (after the user is created)
• The user's password is encrypted and saved in SQL

Because all GP users are part of the DYNGRP role, any GP user could log into SQL through the back-end and gain access to pretty much all the data in the GP databases assigned to them - if it weren't for that last line item in the list. Because the passwords are encrypted, users are limited to accessing SQL data through GP.

Now on to the 'sa' and DYNSA users, internal GP Security, the POWERUSER Security Role, and Alternate/Modified Forms and Reports settings.

sa

This is the big cheese of both SQL and GP. There is no greater user than this and there is no GP data it cannot touch. "There is the GP system password," the semi-experienced might say. Well the 'sa' user does not need to be logged in to GP to get to the SQL tables protected by the GP system password.

The ‘sa’ user is also the only GP user who has direct access to the databases from SQL and is the only SQL user that is created outside GP that can log into GP. A special static ‘sa’ user is built into GP, and it cannot be deleted nor have its password changed from inside GP. The 'sa' user is the only GP user who can perform all GP maintenance and administrative tasks inside GP* as well as all maintenance and administrative tasks in SQL. By default, 'sa' has the POWERUSER Role in all companies.

DYNSA

All GP SQL databases are owned by the DYNSA user. This is what allows the DYNSA user to perform most administrative and maintenance tasks without requiring the ‘sa’ user. 

EDIT: This also allows DYNSA to back up company databases inside GP (but cannot restore them - only 'sa' can restore databases). GP adds DYNSA to the SecrityAdmin and dbCreator system roles on installation, so this user can administer GP users but still does not allow creating new GP companies despite the dbCreator role.

System Password

There are certain system-wide administrative tasks that are protected by GP's System Password, not to be confused with the 'sa' password. The System Password protects areas such as GP Security, Multicurrency Access, and other tasks not related to any particular company. You can log in as 'sa' or DYNSA, but if you do not have the System Password you cannot perform those tasks inside GP. Keep in mind that users do not have to be 'sa' or DYNSA to perform some of those tasks as long as they have the System Password.

Internal GP Security

Dynamics GP's security is what keeps regular GP users from having access to all objects in all GP databases given through the DYNGRP SQL database group.

What I describe here relates to Dynamics GP version 10 and above. Version 9 and below security was completely different.

GP Security Operations

Operations are the individual windows and reports to which security access can be given. For example, the Payables Transaction Entry window and Financial Detail Trial Balance Report are Operations.

Operations are defined by the available windows ("forms," in GP terminology) and reports in a given product dictionary. This is not something you will set up as a GP administrator.

GP Security Tasks

Tasks are small groups of Operations that relate to each other. For example, the ADMIN_PURCH_001 task gives access to several windows and reports relating to setting up Purchasing.

GP Security Roles

Roles are groups of Tasks that define a job role. For example, the AP Clerk role allows a user to enter Payables Transactions, maintain Vendors, look up GL entries, and print Purchasing reports.

There are plenty of pre-defined Tasks and Roles available out of the box. I suggest that instead of editing the defaults you create new Tasks and Roles based on the default ones. This will help you or your Microsoft Partner identify which ones are non-standard if you run into any issues related to security.

When giving access to users, you will be specifying which Role(s) they will have and for which company. A user can have multiple Roles and can have different Roles in each GP company.

POWERUSER Role

Unlike the AP Clerk Role where its access is explicitly defined, the POWERUSER Role implicitely has access to everying the DYNGRP database role has access to in a specified GP company database. The 'sa' and DYNSA users are automatically part of the POWERUSER Role. Giving a regular user all other Security Roles is not the equivalent of the POWERUSER Role.

Alternate/Modified Forms and Reports Settings

In Dynamics GP version 9 and below, setting someone up to use a modified report was very granular and did not allow for very easy administration. In version 10 and later, it is like setting up a group of on/off switches: for each alternate or modified option available on the workstation you are using to set this up, you will specify whether to use the default or the alternate/modified alternative. Yes, since you can technically be using different report dictionaries and different modules per workstation, the Alternate/Modified Forms and Reports settings are workstation-specific; most of the time you'll want to utilize the same modules and a shared location for modified reports, but that's not always the case. Alternate/Modified Forms and Report settings can be specified per user per company.

*Adding Additional System Security

As noted by Devo (thanks for that find!), additional security can be added to users to perform certain administrative tasks, such as adding users and companies. See pages 37-40 of the GP Security Planning guide found here.

EDIT: To give users access to be able to create new users or update passwords, assign the SQL login to the SecurityAdmin server role. To give users access to be able to add new companies in GP Utilities, assign the SQL login to the sysadmin server role.

Conclusion

I hope this helps answer at least a few questions regarding how Microsoft Dynamics GP and Microsoft SQL Server work together to provide security for Dynamics GP as well as clarifies how security is implemented inside GP.

If you found this helpful, please leave a comment.

SQL Reports for GP 10 / 2010 Fun

There are three things to know when setting up the Dynamics GP SQL Reports:

1) You need to have your registration keys entered

The first indication that something is going wrong shows up on the screen where you "select the module(s) to deploy reports for" - and I don't mean because there is a preposition at the end of the sentence. There should be a list of modules here other than "Charts and KPIs." That indication aside, when you get through the wizard and look at your SQL Report Manager site, you will find that nothing more than a couple Data Sources and Report Models were actually created.

Register your Dynamics GP inside GP at Tools >> Setup >> System >> Registration

2) You need to use a not-so-obvious URL in the Reporting Tools Setup window

You will get a friendly "The Report Server URL entered is not valid." warning when you enter the standard Report Server URL that you've used everywhere else you've been asked. No, GP requires something a little different.

For Reporting Services 2005, use
http://ServerName:PortNumber/reportserver/reportservice.asmx

For Reporting Services 2008 (Native Mode), use:
http://ServerName:PortNumber/reportserver/reportservice2005.asmx

For Reporting Services 2008 (SharePoint Integrated Mode), use:
http://ServerName:PortNumber/reportserver/reportservice2006.asmx

UPDATE: It appears that GP 2010 R2 does not need this special link. http://ServerName:PortNumber/reportserver is all that is required.

You may instead get an error regarding "set maxRequestLength='20690' in the web.config file". Edit the web.config found in the ReportServer folder in the SQL Reporting Services program folder; it would be similar to C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer.

Add the following line to the web.config file just above the <securityPolicy> section. If there already is an httpRuntime line, change it to match this statement:

<httpRuntime executionTimeout="9000" maxRequestLength="20960"/>

3) You need to point to a default SQL instance

With the third issue, and you'll have to correct me if this has changed, but I remember running into issues when I had a named SQL instance for Dynamics GP. There really wasn't much that could be done to get the reports deployed. Sorry, no solution here for that one.

SQL Transaction Log Viewer - Poor Man's Version

I came up with a way to somewhat see what is in the SQL Transaction Log in conjunction with the SQL Profiler. So, in addition to seeing the actual SQL statements in the SQL Profiler, you also get an idea, cryptic as it may be, of what those statements are doing to the database. You will need to stop the transaction log backups while you are using this since the transactions, once backed up, will not be returned in our select statement. Note that the Transaction Log only keeps track of changes to the database, which is why we have to run a SQL Profiler trace as well.

The Content0 column is the main payload. There are a couple WHERE statements there to help filter out some of the noise and to look at a particular SQL session (a user's connection) and/or timeframe.

The first script is the my_HexToChar function which translates some of the transaction log information to near-plain-english. You will need to run this script in each database for which you want to look at the transaction log information.

The second and third scripts are for looking at the transaction log information. The first script looks only at the transaction log. The second looks at both the transaction log and the SQL Profiler trace output.

In order to use the third script, you will need to start and run a SQL Profiler trace. Open SQL Profiler, create a new Trace, choose the TSQL_Replay template, check the box for Save to table, select a database other than the one for which you are tracing (I might suggest creating a new DB called ProfilerDB for this purpose), and specify table myReplay. Run the trace and leave it running while you run your queries or programs.

EDIT:
To filter the date range of the returned records, use the commented-out WHERE statement filter for the [Begin Time] column in script 2 or 3.

You can use the fourth script to clear out the trace log and reset the starting point of the transaction log query. If you are wondering what CHECKPOINT is doing to your log/data (it's not clearing out your transaction log), here is a great article from Paul Randal.

SQL Profiler Settings

Script 1: my_HexToChar function

CREATE FUNCTION my_HexToChar (@in VARBINARY(4000))
RETURNS varchar(4000)
AS
  BEGIN
      DECLARE @result varchar(4000)
      DECLARE @i int
      SET @result = ''
      SET @i = 1
      WHILE @i < Len(@in)
        BEGIN
            IF Substring(@in, @i, 1) BETWEEN 0x20 and 0x7A
              -- limiting it to certain visible characters 
              SET @result = @result + cast(Substring(@in, @i, 1) as char(1))
            SET @i = @i + 1
        END
      RETURN @result
  END
GO 

Script 2: view Transaction Log only

SELECT 
    [Begin Time],
    [Transaction Name],
    AllocUnitName,
    [Transaction ID],
    SPID,
    Operation,
    dbo.my_HexToChar([RowLog Contents 0]) Contents0, -- gives an idea of what was changed
    dbo.my_HexToChar([RowLog Contents 1]) Contents1,
    dbo.my_HexToChar([RowLog Contents 2]) Contents2,
    dbo.my_HexToChar([RowLog Contents 3]) Contents3,
    dbo.my_HexToChar([RowLog Contents 4]) Contents4,
    dbo.my_HexToChar([Log Record]) AllContents,
    *
FROM
    fn_dblog(null, null) a
WHERE
    [Transaction ID] in (
        SELECT [Transaction ID]
        FROM fn_dblog(null,null)
        where Operation = 'LOP_BEGIN_XACT'
        and [Transaction Name] not IN ('AutoCreateQPStats','SplitPage','SpaceAlloc','UpdateQPStats')

--        and SPID = 51    -- uncomment this line to limit it to a particular SQL session - look in Activity Monitor for SPIDS

--        and cast([Begin Time] as DATETIME) BETWEEN '07/30/2010 11:30' and '07/30/2010 2:50pm'   -- uncomment this line to limit by date/time
    )
order by a.[Transaction ID],a.[Current LSN]

Script 3: view Transaction Log and SQL Profiler info

SELECT
    *
FROM
(
    SELECT 
        b.[Begin Time],
        [Transaction Name],
        AllocUnitName,
        a.[Transaction ID],
        b.SPID,
        Operation,
        dbo.my_HexToChar([RowLog Contents 0]) Contents0, -- gives an idea of what was changed
        dbo.my_HexToChar([RowLog Contents 1]) Contents1,
        dbo.my_HexToChar([RowLog Contents 2]) Contents2,
        dbo.my_HexToChar([RowLog Contents 3]) Contents3,
        dbo.my_HexToChar([RowLog Contents 4]) Contents4,
        dbo.my_HexToChar([Log Record]) AllContents,
        2 EventType,
        [Current LSN] SortOrder
    FROM
        fn_dblog(null, null) a
    INNER JOIN
        (SELECT max([Begin Time]) [Begin Time],[Transaction ID],max(SPID) SPID FROM fn_dblog(null, null) GROUP BY [Transaction ID] ) b
    ON a.[Transaction ID] = b.[Transaction ID]
    WHERE
        a.[Transaction ID] in (
            SELECT [Transaction ID]
            FROM fn_dblog(null,null)
            where Operation = 'LOP_BEGIN_XACT'
            and [Transaction Name] not IN ('AutoCreateQPStats','SplitPage','SpaceAlloc','UpdateQPStats')
        )
    UNION ALL
    SELECT
        StartTime,
        '',
        '',
        convert(varchar(20),ClientProcessID),
        SPID,
        '',
        TextData,
        ApplicationName,
        '',
        '',
        '',
        '',
        1 EventType,
        convert(varchar(20),EventSequence) SortOrder
    FROM    
        ProfilerDB..myReplay
    WHERE
        DatabaseName = DB_NAME()
) Logs
WHERE
    1=1
    -- and SPID = 60    -- uncomment this line to limit it to a particular SQL session - look in Activity Monitor for it
    -- and cast([Begin Time] as DATETIME) BETWEEN '03/13/2011 18:24:05' and '03/13/2011 18:26:39'   -- uncomment this line to limit by date/time
order by [Begin Time],[Transaction ID],SortOrder

Script 4: clear the Profiler Log and reset the Transaction Log query start point

DELETE FROM ProfilerDB..myReplay
CHECKPOINT

Dynamics GP Security Reports for SQL Reporting Services

User Roles User Tasks User Operations

It is not very easy to find out the current status of everyone’s security in GP. Sure, there are a some standard reports inside GP, but they are not the most user-friendly way to quickly track down who has what permissions to GP’s windows and reports.
That is exactly why we created GP Security Reports for SQL Reporting Services.
There are currently 9 reports in this package to give you at-a-glance, drill-down views of your Dynamics GP security:

1. Role Tasks
2. Task Operations
3. Company Roles – Per User
4. Company Roles – All Users
5. User Roles – Per Company
6. User Roles – All Companies
7. User Tasks – Per Company
8. User Tasks – All Companies
9. User Operations – Per Company

If you would like to find how to purchase these security reports, e-mail info@combussol.com or call 561-392-8135.

GP Security Upgrades

We still have customers upgrading to Dynamics GP 10 or 2010 from version 9 and, even scarier, from version 8. One of the biggest changes starting in version 10 is the security model. The absolute management nightmare of previous versions was changed to something resembling an Active Directory-ish "resource - task group - role group - user" model. This was Microsoft's best addition to GP 10.

When upgrading to version 10 or 2010 from version 9, GP Utilities asks if it should upgrade security. No. NO! Resist the temptation to take the fast track! You will only bring over the junk you are running away from. Start fresh, clean, and clutter-free.

Starting fresh with security also gives the added value of revisiting security requirements. More than a few of our customers did not want to mess with the over-complicated security model of old and just left everyone with access to everything. Not the Sarbanes-Oxley approach, but they had accounting to attend to. When they did take the time to implement security their users would start out with the default permissions assigned by their User Class, and that would be the last time their security ever matched the defaults. A report "I need to run for So-And-So" here, a window "I have to enter in transactions while So-And-So is on vacation" there - all undocumented, of course, and no one would have the guts to reset them back to the default User Class because that would be a phone call from an unhappy user who "can't do anything" just waiting to happen.

Version 10/2010's Security Roles have given nearly all of our customers a reason to implement real security. It's a whole lot easier to figure that their AP Clerk gets the AP Clerk role and that additional permissions are given through the additional role created just for those additions. As far as whether or not to edit the default roles, my suggestion is to leave the default roles alone and either create new ones based on the defaults or create new ones with just the additional permissions. Someone's on vacation? You can give their role to the their underpaid grunt and take it back upon return. One checkbox.

One thing to keep in mind is that you cannot deny access - only allow access. Maybe you have several people performing the same tasks except for one of them who shouldn't see a handful of those windows or reports. You would create one role with all the common tasks and another with the exceptions.

My unique contribution to this subject is this security worksheet to help get started with version 10's security before the upgrade. This is a .zip file with two Excel spreadsheets. One is mainly designed to show the default Tasks contained within each default Role, and the other is a worksheet to assign Users to Roles. Both are derived from a set of SQL Reports we created in-house to give a better visualization than the canned GP security reports. My next post explains more about those reports.

Download the security worksheets here