Forecaster Database Transfer Tool Permissions

A shout-out to Jake Friedl at Microsoft for pointing us in the right direction on this one. If you are using Forecaster with Management Reporter, you probably have been faced with using the Forecaster Database Transfer Tool to move the budget information from Forecaster to Microsoft Dynamics GP so that you can report on it out of Management Reporter.


Generally, this works great and is pretty easy to use. However, the need to run the tool on a regular basis to get the latest numbers on reports makes me miss FRx and DirectLink-- I cannot believe I just admitted to that :) It may make sense to give your budget administrators permissions to use the Transfer Tool to update the balances whenever they need. Since the tool starts out with asking you for credentials for both the Forecaster and the Dynamics GP databases, you will need to set up the proper permissions in SQL Server for a non-administrator user to run the tool successfully.

Since these steps involved modifying stored procedures as well as permissions, it is recommended that you are comfortable with SQL Server before proceeding and have recent backups of your Dynamics GP company and Forecaster databases.

Also, another side note, the first time you access the Forecaster Database Transfer Tool, there are some SQL Server Objects that need to be created. So, the first user who uses the tool must have dbo or db_ddladmin roles in both the Dynamics GP and Forecaster databases. Otherwise, the user can use the Create to File function to send the scripts to an administrator to be run. I am NOT recommending that you give either of these roles to a non-IT administrator, just that you might want to have an IT/SQL administrator run through the transfer the first time to avoid these issues.
So, let’s start with the GP side of things….

1. The user login that will be used to access the Forecaster Data Transfer Tool needs to set up as a user in the SQL Server under Security-Logins. This can be a windows user, or a specific SQL user, depending on how you want to administer the account. It CANNOT be an existing Dynamics GP login. When you set up the user, they must be assigned to your Dynamics GP company database with the DYNGRP role in the User Mapping section of the Login Properties window.

2. You need to modify the stored procedure FCBUDGETIMPORT in your Dynamics GP company database to comment out the last section. To do this, expand your Dynamics GP company-Programmability-Stored Procedures and locate FCBUDGETIMPORT. Right-click and choose Modify. Once you comment out the last section as noted in the screenshot below, click the Execute icon at the top of the Query window. This modification ensures that an error will not be produced each time the stored procedure is executed.

3. Update the permissions for the DYNGRP role by running the following scripts, substitute your Dynamics GP company database for [GPDatabase] in the first two scripts.


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, things are a bit simpler…

1. The user must be added to the Forecaster database with at least the db_datareader role. This is done by expanding Security-Logins-Right-click on user and choose Properties. Database and role assignment is in the User Mapping section.

Good luck! Let me know if you run in to any issues, and thanks again to Jake for sharing the scoop!

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.