Record Permissions with SQL Profiler

With version 1.20 or later of Easy Security is it possible to use the SQL Profiler to record permissions. This is then used in Easy Security as a Recording similar to a Recording done with the Client Monitor. The big advantage of using the SL 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.

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 user, databases, application 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 has now been added and the normal process in Easy Security can be used to build Roles.

Manually adding the SQL Profiler templates

Two event must be tracked "RPC:Starting" and "SP:StmtCompleted" the screen shots below display the columns tracked for each. The "ApplicationName", "LoginName", "SPID" and "TextData" is 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