October 20, 2011 at 3:18 pm
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
October 20, 2011 at 3:29 pm
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/
October 20, 2011 at 3:32 pm
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
October 20, 2011 at 3:37 pm
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?
October 20, 2011 at 3:43 pm
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/
October 20, 2011 at 3:50 pm
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:
It's pretty straight-forward
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 21, 2011 at 7:46 am
Thanks for your help.
Is there a way to get the user, server name, application name of the one who makes the change?
Thanks
October 21, 2011 at 7:55 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy