Error message when you try to open the SmartView window in Microsoft Dynamics GP: "EXECUTE permission denied on object 'zDP_MXLS0011F_1' database 'xx’, owner 'dbo'"

Applies to: Dynamics GP 2013Dynamics GP 2010

Symptoms


You try to open the SmartView (Audit Trail) window after you install Audit Trails in Microsoft Dynamics GP or in Microsoft Business Solutions - Great Plains. In this situation, you receive the following error message(s):


A get/change first operation on table 'mxAuditTables' failed accessing SQL data.
The EXECUTE permission was denied on the object 'zDP_MXLS0011F_1', database 'xx', schema 'dbo'.

A get/change first operation on table 'mxAuditEvents' failed accessing SQL data.
The EXECUTE permission was denied on the object 'zDP_MXLS7000_1', database 'xx', schema 'dbo'.

A get/change first operation on table 'mxAuditSetup' failed accessing SQL data.
The EXECUTE permission was denied on the object 'zDP_MXLS4000SS_1', database 'xx', schema 'dbo'. 

Cause


This problem may occur if permissions are not applied correctly to the Microsoft Dynamics GP company database or the Microsoft Business Solutions - Great Plains company database when Audit Trails is being installed.

Resolution


To resolve this problem, run the Grant.sql script against the Microsoft Dynamics GP company database.


Microsoft Dynamics GP 2013, Microsoft Dynamics GP 2010 and Microsoft Dynamics GP 10.0:

Run the grant.sql script against the company database and dynamics database as follows:
1. Make a current backup for safe-keeping.

2. Ask all users to exit GP. 

3. Path out to your Dynamics GP Code Folder on the server: A typical location is C:\Program Files\Microsoft Dynamics\GP\SQL\UTIL.

   Note:  If you have files in this folder, go to step 4. However, if you do not have any files in the UTIL folder, then you will need to extract the files using the steps below: 

a. Have all users exit GP.
     b. Navigate to C:\Program Files\Microsoft Dynamics\GP\Data and open the DEX.INI file in Notepad.

     c. Add the line below to the end of the dex.ini file.       
duExtractSqlResources=TRUE

     d. Then close the file, saving your changes.

     e. Now run ‘GP utilities’ to extract the files and check the path in step 3 again and the grant.sql file should be there now.

4. Find the Grant.sql file in the UTIL folder and copy it into a query window in SQL Server Management Studio. (If you double-click it, it should open in SQL Server Management Studio if you have it open. If not, then open it with Notepad and copy and paste it into a SQL query window.)

5. Run it against the DYNAMICS database.
6. Then run it against the Company database.
7. Have the user test again.



Microsoft Dynamics GP 9.0 and Microsoft Business Solutions - Great Plains 8.0

To resolve this problem, run the Grant.sql script against the Microsoft Dynamics GP company database or the Microsoft Business Solutions - Great Plains company database. To do this, follow these steps:
  1. Back up the Microsoft Dynamics GP company database or the Microsoft Business Solutions - Great Plains company database.
  2. Follow the appropriate step, depending on whether you are using Microsoft SQL Server or Microsoft SQL Server Desktop Engine (also known as MSDE 2000):
    • If you are using SQL Server 2000, start SQL Query Analyzer. To do this, click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
    • If you are using SQL Server 2005, start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
    • If you are using MSDE 2000, start the Support Administrator Console. To do this, click Start, point to All Programs, point to Microsoft Administrator Console, and then click Support Administrator Console.
  3. On the File menu, click Open.
  4. Locate and then open the Grant.sql script. To do this, follow the appropriate step:
    • If you are using Microsoft Dynamics GP, the file is located in the following folder:
      %programfiles%\Microsoft Dynamics\GP\Sql\Util
    • If you are using Microsoft Business Solutions - Great Plains:
      %programfiles%\Microsoft Business Solutions\Great Plains\Sql\Util
  5. On the toolbar, click the Microsoft Dynamics GP company database or the Microsoft Business Solutions - Great Plains company database in the Database list.
  6. Run the script against the company database. To do this, click Query, and then click Execute.
  7. Start Microsoft Dynamics GP or Microsoft Business Solutions - Great Plains.