Record Permissions with SQL Profiler

With version 1.20 or later of Easy Security, it is possible to use the SQL Profiler to record permissions. The trace is then used in Easy Security as a Recording, similar to a Recording done with the Client Monitor. The big advantage of using the SQL Profiler is the ability to record permissions for transactions from the RoleTailored Client, Classic Client, Navision Application Server and Web-services. Even recording the permissions required during login and logout are possible.

Special templates have been created to minimize the amount of data captured. These templates are released together with the Easy Security product and can be imported to the SQL Profiler. Refer to the bottom of the page for manually adding the templates.

When recording from the Classic Client, either set object cache to zero or close and reopen the database before you start the recording. This will ensure that all the object activities performed during the recording process are captured.
When recording from RTC, either set object cache to zero or restart the NAV Server service before starting a recording. In RTC, objects are cached on the server, not on the client. In order to avoid disrupting other users when restarting the NAV service, we recommend setting up a separate NAV Server instance to be used for recordings.
See Installing additional NAV Service Tier for recording of permissions

Also How to set Object Cache to zero for recording

To record permissions with the SQL Profiler, follow the steps below.
1: Launch SQL Profiler and connect to the right SQL Server

2: Start a new Trace using the NAV Easy Security template. This can be limited to only capture for certain users, databases, applications and a lot more. Please refer to the SQL documentation for additional help on this.

3: Perform transactions in NAV or use Web-service.

4: In the SQL Profiler, stop the trace and export to a "Trace XML File".

5: Create a new recording in Easy Security, lookup in the "SQL Profiler Trace File Name" to the file saved in step 4.

6: Use the function "Import SQL Profiler Trace"

7: Select the users or companies that should be added to the Recording Permissions. The field "Include in Rec. Permissions" can be unchecked if not all transaction needs to be added. Click OK.

8: The Recording Permissions have now been added and the normal process in Easy Security can be used to build Roles.

Manually adding the SQL Profiler templates

Two events must be tracked - "RPC:Starting" and "SP:StmtCompleted". The screen shots below display the columns tracked for each. The "ApplicationName", "LoginName", "SPID" and "TextData" are mandatory. The other fields are nice to have.

The SQL 2008 Profiler, from SQL 2008 R2, has been tested with SQL 2005, SQL 2008 and SQL 2008 R2 databases.

Don't Like

Related resources

Download software from Mergetool.com