Create Hidden Trace / Mask Trace

Question

Create Hidden Trace / Mask Trace

mitchismoney

Mr or Mrs. 500

Points: 533
More actions
October 18, 2010 at 5:56 pm

#141193

Hello all,
I am wondering if there is a way to run a trace on a specific user and not have the user know about it. The user currently has dbo permissions, so they have quite a bit of access.
My question is this: is there a way to run a trace on this individual or mask a trace to look like a background operation so that if they run sp_who2 or something they won't be able to identify it?
Thanks in advance.
_______________________________________________________________
Its not how far you go, its how go you far...:crazy:

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

a server side trace can be seen in sys.traces, but does not show up as a spid process the way it would when you run Profiler.

that said, you have some real issues if you need to be spying on someone else with dbo privileges.

it's not easy to decode what the events the trace is tracking and which filters are applied, but it's trivial to see the results of the trace itself.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Leo.Miller SSChampion Points: 13051 More actions · Answer 2

What sort of information are you trying to capture? Have you looked at what the Default Trace gives you?

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

mitchismoney Mr or Mrs. 500 Points: 533 More actions · Answer 3

It's not an ideal situation we are in, but we want to be able to track or watch for any, oh, lets say malicious activity this individual may attempt. Ultimately I want to "keep an eye on" this person daily. Look for access at odd times of the night, data extraction/copying. That sort of thing.

If it's not going to show up as a spid I might just go that route.

Thanks!

_______________________________________________________________
Its not how far you go, its how go you far...:crazy:

Leo.Miller SSChampion Points: 13051 More actions · Answer 4

Unless the user suspects you are tracing them and runs:

SELECT * FROM :: fn_trace_getinfo(0)

and then runs:

SELECT * FROM :: fn_trace_getfilterinfo (trace_id),

They won't see the trace.

You could block their execute rights on the relevant functions. Also if they are only DBOs on selected databases they may not be able to execute these functions anyway. I'd have to test a bit.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

mitchismoney Mr or Mrs. 500 Points: 533 More actions · Answer 5

Great, thanks Leo. I'll do some testing with that tomorrow and see what the results are.

I'm hoping we don't experience anything bad, but if I can do even a little in an attempt to protect the data I have to try!

_______________________________________________________________
Its not how far you go, its how go you far...:crazy:

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 6

If you just take away their trace permissions on the server, they'll never know. Do they currently use trace? If not, they'll never miss those privs. If so, there's little you can do to prevent them checking to see if a trace is in place.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mitchismoney Mr or Mrs. 500 Points: 533 More actions · Answer 7

The user doesn't have access to profiler. Will revoking access to trace work even DBCC Traceon? I didn't think you could deny specific DBCC commands?

Thanks

_______________________________________________________________
Its not how far you go, its how go you far...:crazy:

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 8

mitchismoney (10/19/2010)
The user doesn't have access to profiler. Will revoking access to trace work even DBCC Traceon? I didn't think you could deny specific DBCC commands?
Thanks

You can't deny the DBCC command, but you can deny the server level permission ALTER TRACE, which will prevent access to the trace functions.

DBCC TRACEON is for setting trace flags, which is different than modifying a trace, similar names not withstanding.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning