Navision Application Roles

Navision secuirty works on application roles which are created in the SQL Server. For each user that is created in Navision an application role is created and the effective permissions for the user are applied to the appplication role by the navision client.

During a performance tuning excercise I modified some of the SIFT tables to increase the performance. The problem set in when due to these changes the navision security got disturbed and the users were automatically revoked permissions on the SIFT tables. I was required to constantly make changes to the SIFT tables and it was painful to synchronize all the user again and again so i figured out that we could grant the access permissions to these tables for all the application roles which would solve the problem and not require the login synchronization each time.

To get a list of all the application roles i used the following query :
select name 'rolename', uid 'roleid', isapprole from sysusers where isapprole = 1

The grant query was also easy to generate as shown below
select 'grant select, insert, update on [TableName] to ' + name
from sysusers where isapprole = 1