How to audit insert/update in a specific table

Question

How to audit insert/update in a specific table

Rem70Rem

SSCertifiable

Points: 6166
More actions
October 20, 2011 at 3:18 pm

#259138

Hi,
I am trying to figure a way to audit all update/insert done on specific table. When I use Profiler with the filter on text data, it works when it's a DDL. But a lot of our code is done via store procedures and what I see in the Profiler is sp_execute..... So I can't see the information related to the query.
How a can monitor the insert/update on a specific table without creating triggers? I want to find who (user, command, host server) is inserting/updating in a specific table.
Thanks

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

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

Why without triggers? That is what they are good at. You pretty much have two choices, triggers or CDC (Change Data Capture).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

MyDoggieJessie SSC-Forever Points: 44519 More actions · Answer 2

Without using a 3rd party tool or triggers I don't think you have much left to work with. In futhering what Sean has said: http://msdn.microsoft.com/en-us/library/bb522489.aspx

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 3

I have 2 fields in a tables that are updated. I want to find which application/user/DDL is doing it.

How I can set a trigger to get these information?

Sean Lange SSC Guru Points: 286609 More actions · Answer 4

Check out the link from MyDogJessie or this link from BOL. http://msdn.microsoft.com/en-us/library/ms189799.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

MyDoggieJessie SSC-Forever Points: 44519 More actions · Answer 5

Actually, from one of the links on the page I offered there's a link to an example of exactly what you need to do to implement it:

https://community.altiusconsulting.com/blogs/altiustechblog/archive/2009/09/07/sql-server-2008-change-data-capture-example.aspx

It's pretty straight-forward

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 6

Thanks for your help.

Is there a way to get the user, server name, application name of the one who makes the change?

Thanks

okbangas SSChampion Points: 11773 More actions · Answer 7

At least you've got user_name(), suser_name(), @@servername and @@servicename.

select * from sys.dm_exec_sessions where session_id = @@SPID

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Lowell SSC Guru Points: 323546 More actions · Answer 8

okbangas (10/21/2011)
At least you've got user_name(), suser_name(), @@servername and @@servicename.

In addition to what my friend here psoted, I usually start with this inside an auditing trigger, and add additional columns, or remove those i do not need.

--the auditing snippet below works fine in a

--login trigger,

--database trigger

--or any stored procedure.

SELECT

getdate() AS EventDate,

DB_NAME() AS DBName,

HOST_NAME() AS HostName,

APP_NAME() AS ApplicationName,

OBJECT_NAME(@@PROCID) AS ProcedureName,

USER_ID() AS Userid,

USER_NAME() AS UserName,

SUSER_ID() AS sUserid,

SUSER_SNAME() AS sUserName,

IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

IS_MEMBER('db_owner') AS [Is_DB_owner],

IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

client_net_address AS ipaddress,

auth_scheme AS AuthenticationType

FROM sys.dm_exec_connections where session_id = @@spid

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!