Dynamics Community Forum Thread Details

Field Level Security and SQL sysadmin rights

Few months back, one of my clients reported an issue with the field level security module in Dynamics GP that it only works for “Sa”, and this morning I noticed a question in the community reminding me to post about this issue! Below are the circumstances:

1. Only the sa user can see the module on GP even by giving the power user to other users.

2. The restrictions made on the field level security module are applied only to sa user.

Solution clearly shows that this is not an issue with the application and it is an issue with database privileges, simply go to “C:\Program Files\Microsoft Dynamics\GP2010\SQL\Util” and locate a script called “Grant.SQL”, the script will grant the DYNGRP role in SQL the access on all objects in the database and will resolve your issue, below the script if needed:

/*Count : 1 */

declare @cStatement varchar(255)

declare G_cursor CURSOR for select 'grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects 
 where (type = 'U' or type = 'V') and uid = 1

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement 
WHILE (@@FETCH_STATUS <> -1)
begin
 EXEC (@cStatement)
 FETCH NEXT FROM G_cursor INTO @cStatement 
end
DEALLOCATE G_cursor

declare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects 
 where type = 'P' 

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement 
WHILE (@@FETCH_STATUS <> -1)
begin
 EXEC (@cStatement)
 FETCH NEXT FROM G_cursor INTO @cStatement 
end
DEALLOCATE G_cursor