Brain racking for fun (Can you track/capture a select from a specific Login)

Question

Brain racking for fun (Can you track/capture a select from a specific Login)

bopeavy

SSCertifiable

Points: 5869
More actions
July 6, 2011 at 8:23 am

#389907

The title says it all! I want to know is there a DMV or anything in sql that logs what is happening with a select before the actual select happens? You know can you track/capture a select from a specific Login?
:cool:

"There are no problems! Only solutions that have yet to be discovered!"

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Server-side trace. The T-SQL:StmtStarting event

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bopeavy SSCertifiable Points: 5869 More actions · Answer 2

Gail

I take it that would not allow me to do anything before it has taken place tho?

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Lowell SSC Guru Points: 323518 More actions · Answer 3

i'm with Gail; all you can do is trace it...so i don't think so; for example the DMV's that have the execution plans for a given statement would occur after the statement was started, but maybe before the statement was completed...

are you trying to prevent a select from occurring, or do you need to audit/track a select? maybe there's a different way to do what you are after.

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!

bopeavy SSCertifiable Points: 5869 More actions · Answer 4

No I actually want to capture it and alter then return the results before returning values!

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Write a stored procedure and make them call that rather than running ad-hoc queries.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bopeavy SSCertifiable Points: 5869 More actions · Answer 6

Write a stored procedure and make them call that rather than running ad-hoc queries.
--------------------------------------------------------------------------------
Gail Shaw
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail

Interesting but how do you capture the select with a stored proc? I can not use a trigger because it does not have a way to kick off on the select.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

You don't. You tell whoever wrote the app that they must call stored procs not run adhoc queries.

Frankly the idea of modifying the returned data is terrifying. If I could send a SELECT * FROM foo but get back the rows from the table bar instead it could be a major mess.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 8

GilaMonster (7/6/2011)
You don't. You tell whoever wrote the app that they must call stored procs not run adhoc queries.
Frankly the idea of modifying the returned data is terrifying. If I could send a SELECT * FROM foo but get back the rows from the table bar instead it could be a major mess.

I've seen views that manipulate data based on username, or if the user is in a certain role or not...you know, so it's hides credit card numbers and stuff like that.

unless you take away the ability to adhoc query the raw tables, and as gail said, send them the data vie procs or views or something that encompasses that "instead of this givem that", you are stuck. they get what they ask for from the tables.

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!