Excel Reports in Microsoft Dynamics GP - Security Setup

  • Comments 2

I’ve had several cases concerning user access to Excel reports created and published using Excel Report Builder lately.  I’d like to cover the main issue we have seen when setting this up.

The first thing you need to know is that you cannot use your GP user accounts to access the Excel Reports.  The SQL user accounts that are created using GP have their own special encryption applied and will only work when logging in through GP.  You cannot access SQL with GP created SQL accounts using any other program but GP.  The best option is to use Windows authentication which I will walk through setting up now.

When you publish an Excel report through Excel Report Builder you have the option of setting Permissions:

Publish

This is where you set which Database roles will have access to the report:

Permissions

Once you’ve actually set which roles have permissions and published your report you will need to go to SQL Mgmt. Studio and add the users to the roles.  This is where we need to add the users Windows authenticated accounts if they aren’t already setup (they usually aren’t).

- Launch SQL Server Management studio to connect to your SQL server and then navigate to Security > Logins
- This will list all of the users who currently have some type of access to SQL.  We need to add the Windows users here if they are not already added.
- Right click ‘Logins’ and select ‘Properties’  (If the user is already added, right click the user and select Properties)
- Here you will enter the login name of the user using the format of DOMAIN\User
- Then select ‘User Mapping’ on the left hand side

This is where you will actually assign the user to the appropriate role for each company database they need access to.

Role

If you have multiple reports for different companies you need to add them to the role for each company.  You should also add them to the appropriate role for the DYNAMICS database.  Once you’ve set this up and saved it the user will have access to the report data.  The benefit to setting it up this way is that when a user launches the report it will not prompt them for credentials, it will simply work.

I hope this quick walk through on setting up security for Excel reports helps out!  You can find more information about Excel Report Builder in the ‘SmartList Builder User’s Guide’ in your GP Documentation folder.

  • Chris, Can you also AD Security groups to SQL and assign those groups to the Database roles?

  • Hey Bob!  You should be able to do this with a Security group from AD.  I don't see why this wouldn't work as it is allowable in SQL.