Need to gather the source process for a SQL server query

Question

Post reply

Need to gather the source process for a SQL server query

rahulr.sharma

Ten Centuries

Points: 1034
More actions
April 24, 2012 at 2:13 am

#260305

Hello All,
Is there a Stored Proc or a function using which I can recognize the process that fired a SQL Server query on my table/database. My main requirement is to recognize the process like a SQL server agent Job, SSIS, Bulk insert/ update, We application etc. which made the changes to rows in my table.
Background story for this is that I have a table which holds the data of different SQL servers in my organisation. Lets say there are 6 columns in my table : Server name, Server Operating System, Insert process name and Update Process name. There are some SQL Server agent jobs, SSIS packages etc that run each week and check for the servers in the environment and update the server name or OS in the table. What I require is that once the SQL Job process inserts a new row for a server insert process id should become SQL Server Agent Job and the same goes for update of the rows also.
There would be some situations where a user may manually edit these rows using a Web App as well and in that situation I would want to track the Windows account id of the person doing it.
Any suggestions?

Viewing 4 posts - 1 through 3 (of 3 total)

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

vinu512 SSCoach Points: 15729 More actions · Answer 1

Wouldn't a trace to check which query is doing what on which table help?

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Are you looking for Auditing solution?

Depends on the depth of details you want to trace, there are few methods and most common are:

- custom auditing using light-weight audit triggers (still my preference...)

- SQL CDC

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 3

The best way to track T-SQL commands that have been issued in the past is through an extended event session. They're easy to set up and there's a function to load them into tables for querying against the data captured. This is the 2008 introductory documentation. If you're only interested in the data changes themselves, I'd suggest looking into Change Data Capture. This is the 2008 version of that document.

"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