Custom Auditing

We are having to implement restrictive security permissions on SQL, some of
which requires that we audit and log any access of any kind for specific
tables and columns. What would be the best way to do this? We can use
triggers to log any changes to specific tables and columns, but we can't use
those when a user is simply retrieving sensitive and encrypted data from a
table. We could lock down access permissions to those tables so that only
authorized users can execute stored procedures, and have code to log such
access in the procs themselves, but we need to ensure that even if the dbo
or sa were to access that data, that it would be logged.
TIA
Michael MacGregor
Database ArchitectMichael,
If you want to log accesses, even of the dbo or sa, then you pretty much
will have to use Profiler. You can put the traces into tables for online
auditing, either directly or by periodically importing the trace files.
There are also probably some third-party auditing tools.
RLF
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OyGkG4tyHHA.3536@.TK2MSFTNGP06.phx.gbl...
> We are having to implement restrictive security permissions on SQL, some
> of which requires that we audit and log any access of any kind for
> specific tables and columns. What would be the best way to do this? We can
> use triggers to log any changes to specific tables and columns, but we
> can't use those when a user is simply retrieving sensitive and encrypted
> data from a table. We could lock down access permissions to those tables
> so that only authorized users can execute stored procedures, and have code
> to log such access in the procs themselves, but we need to ensure that
> even if the dbo or sa were to access that data, that it would be logged.
> TIA
> Michael MacGregor
> Database Architect
>|||Thanks again Russell.
MTM