How to Verify Security Auditing Levels
You can verify security auditing levels by querying the appropriate system catalog.
To check that security auditing was enabled on all operations by all users, installation wide, follow these steps:
1. Log on as the installation owner.
2. Connect to master database iidbdb.
Query the system catalog iisecurity_state by issuing the following command:
SELECT STATE FROM iisecurity_state
WHERE NAME = 'All';
The value returned should be E (enabled).
To check that query text auditing was enabled by a specific user, follow these steps:
3. Log on as the installation owner.
4. Connect to master database iidbdb.
5. Query the system catalog iiusers by issuing the following command:
SELECT AUDIT_QUERY_TEXT FROM iiusers
WHERE USER_NAME = ‘username’;
The value returned should be Y.