Hi, I have a general high level question. Given an extremely large database
what would be the best way to track anytime someone queries (simple select
statement) information in the tables. Any read of the data requires that
what was read be tracked for security purposes. I've thought about a couple
ideas setting triggers that are fired on a select, but I've never done this
and not even sure if it's possible. Not sure what logging capabilities SQL
Server has for select statements, I would think this is problematic given the
size of the database (hundreds of millions of records). And finally,
probably the direction I'm leaning. building a trigger that tracks processes,
specifially what the select statement was.

Any ideas on this or best practice would be much apprecaited?

Thanks,

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...erver/200806/1

On Jun 16, 3:55pm, "doverj via SQLMonster.com" <u42617@uwe> wrote:
> Hi, I have a general high level question. Given an extremely large database
> what would be the best way to track anytime someone queries (simple select
> statement) information in the tables. Any read of the data requires that
> what was read be tracked for security purposes. I've thought about a couple
> ideas setting triggers that are fired on a select, but I've never done this
> and not even sure if it's possible. Not sure what logging capabilities SQL
> Server has for select statements, I would think this is problematic given the
> size of the database (hundreds of millions of records). And finally,
> probably the direction I'm leaning. building a trigger that tracks processes,
> specifially what the select statement was.
>
> Any ideas on this or best practice would be much apprecaited?
>
> Thanks,
>
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200806/1

Triggers aren't going to you, there is no SELECT trigger in SQL
Server. Read up on trace logs (NOT Profiler), these will provide
exactly what you're looking for.

If you can control all the access through stored procedures, you can do your
logging in the stored procedures.

Linchi

"doverj via SQLMonster.com" wrote:

> Hi, I have a general high level question. Given an extremely large database
> what would be the best way to track anytime someone queries (simple select
> statement) information in the tables. Any read of the data requires that
> what was read be tracked for security purposes. I've thought about a couple
> ideas setting triggers that are fired on a select, but I've never done this
> and not even sure if it's possible. Not sure what logging capabilities SQL
> Server has for select statements, I would think this is problematic given the
> size of the database (hundreds of millions of records). And finally,
> probably the direction I'm leaning. building a trigger that tracks processes,
> specifially what the select statement was.
>
> Any ideas on this or best practice would be much apprecaited?
>
> Thanks,
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums...erver/200806/1
>
>

Thank you both, unfortunately there will be certain users who will have the
ability to query directly off the tables without going through a stored
procedure so we will have to also monitor everything they can see.

Sounds like from this thread and other postings / information I've seen
tracing is the best way to go.

Thanks again.

Linchi Shea wrote:
>If you can control all the access through stored procedures, you can do your
>logging in the stored procedures.
>
>Linchi
>
>> Hi, I have a general high level question. Given an extremely large database
>> what would be the best way to track anytime someone queries (simple select
>[quoted text clipped - 10 lines]
>>
>> Thanks,

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...erver/200806/1