April 5, 2005 at 3:11 am
I want to log information to a file regarding anyone who can 'view' or 'update' the table content.
I read about Profiler, but options are such that it may flood the logs. The other option is to write a own stored procedure that triggers when an activity is occurs.
Any other suggestions??
April 5, 2005 at 9:09 am
You could use a trigger for update and store the info in an audit table but it's pretty near impossible to track who has viewed a table (unless you only allow them to do it via a SP which in turn logs the user details in an audit table).
If you're worried about people accessing data when they're not meant to then you're better off setting up the security properly rather than logging who's doing what (proactive as opposed to reactive). As you say you could use Profiler to monitor this sort of thing but if that's unworkable for you then your choices are very limited.
April 5, 2005 at 11:40 pm
Thks for your attention.
The concern is any user accessing the table that store audit trail logs. This user could be even dba. Hence, I want to log the activities such as 'view' and 'change to content of the table' (sorry for mentioning incorrectly UPDATE) to a file that will be secured using windows acl.
After spending some time testing Profiler and system sp, I think it is not possible to log the activity of an user viewing the table content. However, this could be overcome by writing a sp for not allowing select option. I'm currenlty working on it will later post it.
Also, the changes (apology for mentioning UPDATE in the previous msg) made to the table content could be logged using 'sp_trace_setevent' with following event and column ids. Correct me otherwise, the following events will log incase the changes are made to the table content.
Event ids
1) 16AttentionOccurs when attention events, such as client interrupt requests or broken client connections happen
2) 18ServiceControlOccurs when the SQL Server service state is modified
3) 50 SQL TransactionTracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK TRANSACTION statements.
4) 82-91 User Configurable (0-9)Event data defined by the user.
5) 117 Audit Change AuditOccurs when audit trace modifications are made
6) 118Audit Object Derived PermissionOccurs when a CREATE, ALTER and DROP object commands are used
Any comments whether the above is the correct solution?
April 5, 2005 at 11:41 pm
Thks for your attention.
The concern is any user accessing the table that store audit trail logs. This user could be even dba. Hence, I want to log the activities such as 'view' and 'change to content of the table' (sorry for mentioning incorrectly UPDATE) to a file that will be secured using windows acl.
After spending some time testing Profiler and system sp, I think it is not possible to log the activity of an user viewing the table content. However, this could be overcome by writing a sp for not allowing select option. I'm currenlty working on it will later post it.
Also, the changes (apology for mentioning UPDATE in the previous msg) made to the table content could be logged using 'sp_trace_setevent' with following event and column ids. Correct me otherwise, the following events will log incase the changes are made to the table content.
Event ids
1) 16AttentionOccurs when attention events, such as client interrupt requests or broken client connections happen
2) 18ServiceControlOccurs when the SQL Server service state is modified
3) 50 SQL TransactionTracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK TRANSACTION statements.
4) 82-91 User Configurable (0-9)Event data defined by the user.
5) 117 Audit Change AuditOccurs when audit trace modifications are made
6) 118Audit Object Derived PermissionOccurs when a CREATE, ALTER and DROP object commands are used
Any comments whether the above is the correct solution?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply