It is commonly thought that the grant.sql script (found in the GP Folder\SQL\Utils\) takes care of security at the SQL Server level by granting access to DYNGRP to all of the SQL objects used by Dynamics GP.
It doesn’t!
We recently had a support case where the user was getting the following error:
The EXECUTE permission was denied on the object ‘MFG_FUNC_Component_BOM_Type’
The function is part of the Manufacturing Module, but our Export Indented BOM feature in MFG PowerPack uses the function.
We asked the client to run grant.sql to fix the issue. The user still had the error.
At that point we knew there were only two options: (1) the User ID wasn’t assigned to DYNGRP, or (2) there was something wrong with grant.sql.
A quick look at grant.sql and the problem was obvious–it grants security on Tables, Views and Stored Procedures. It does not grant security to Scalar Functions or Table Valued Functions.
An updated GRANT.SQL script is below. It covers all of the SQL objects listed above.
Download GrantAll.zip (it contains a text file): GrantALL