Permissions for the Forecaster Database Transfer Tool

Special thanks to Jake Friedl from Microsoft for his guidance on this issue. When using Forecaster with Management Reporter, you’ve likely encountered the need to transfer budget data from Forecaster to Microsoft Dynamics GP using the Forecaster Database Transfer Tool. This allows you to include budget information in your Management Reporter reports.

While this tool is generally efficient and user-friendly, needing to run it regularly for up-to-date numbers can be a drawback. It might be beneficial to grant your budget administrators permission to use the Transfer Tool as needed. Since the tool requires credentials for both Forecaster and Dynamics GP databases, you’ll need to configure appropriate permissions in SQL Server for non-administrator users.

Given that these steps involve modifying stored procedures and permissions, it is highly recommended to have a good understanding of SQL Server and ensure you have recent backups of your Dynamics GP company and Forecaster databases before proceeding.

It’s also worth noting that upon first-time access, the Forecaster Database Transfer Tool requires the creation of certain SQL Server objects. Therefore, the initial user needs dbo or db_ddladmin roles in both the Dynamics GP and Forecaster databases. Alternatively, the user can utilize the “Create to File” function to provide the necessary scripts to an administrator for execution. It is not advisable to grant these roles to non-IT administrators. However, it might be beneficial to have an IT/SQL administrator handle the initial transfer to avoid potential issues.

Let’s begin with the Dynamics GP setup.

  1. Create a SQL Server login under Security-Logins for the user who will be accessing the Forecaster Data Transfer Tool. This can be a Windows user or a SQL Server user. However, it cannot be an existing Dynamics GP login. When setting up the user, ensure they are assigned to your Dynamics GP company database with the DYNGRP role within the User Mapping section of the Login Properties window.

  2. Locate the FCBUDGETIMPORT stored procedure within your Dynamics GP company database (Programmability-Stored Procedures). Right-click and choose Modify. Comment out the last section of the stored procedure as shown in the screenshot below, then click the Execute icon at the top of the Query window. This adjustment prevents an error from occurring each time the stored procedure runs.

  1. Update the DYNGRP role permissions by executing the following scripts. Be sure to replace [GPDatabase] with your Dynamics GP company database name in the first two scripts.

    1
2
3
4
5
6
7
8

    USE [GPDatabase] 
GRANT ALTER ON dbo.EE_GL00201 to DYNGRP

USE [GPDatabase] 
GRANT EXECUTE ON dbo.FCBUDGETIMPORT to DYNGRP

USE DYNAMICS 
GRANT ALTER ON dbo.SA_Forecaster7ImportErrorLog to DYNGRP

On the Forecaster side, the process is more straightforward.

  1. Add the user to the Forecaster database, granting them at least the db_datareader role. You can do this under Security-Logins. Right-click the user and select Properties, then manage database and role assignments in the User Mapping section.

Best of luck! Feel free to reach out if you encounter any difficulties. And once again, thanks to Jake for sharing his expertise!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Licensed under CC BY-NC-SA 4.0