Server Database Audit or Extended Event

Question

Server Database Audit or Extended Event

alex.sqldba

SSChampion

Points: 10254
More actions
May 25, 2016 at 1:36 pm

#314140

Hi Guys,
We have this problem whereby its being reported that some data is being changed when it shouldn't be. Security is pretty tight anyway, access is only via stored procedures, except for the 3 sysadmins.
It is possible that it is an internal process doing this though we've not been able to capture that.
What is better to track this down - Server Audit or an Extended Event?
It's one particular table, a status flag is being altered from 0 to 1.
So I need to capture whether its a procedure updating this row, or a human.
Could I get your thoughts on the best way of tracking down - and if any links to a good example would also be appreciated.
Cheers
Alex

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

i think for whodunnit information, a trigger is going to be the best, followed by a SQL Audit(which uses Extended events anyway...so it's a pre-built tool that writes to the log, vs build your own version in extended events which writes to a file, potentially.)

if you need " who changed rowID 42 at 12:7am" kind of information, a well written trigger, writing to a table that was granted INSERT permission to public would be what i would go with.

i have a code snippet i like to use in a major whodunnit investigation like this:

just grab the primary key so you can link it back,statusflag from the table Where inserted.[statusflag] <> deleted.[statusflag]

-for 2008 and above:

--the auditing snippet below works fine in a

--login trigger,

--database trigger / DML trigger

--or any stored procedure.

SELECT

getdate() AS EventDate,

DB_NAME() AS DBName,

CURRENT_USER AS CurrentUser,

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],

ConnectionProperty('net_transport') AS 'net_transport',

ConnectionProperty('protocol_type') AS 'protocol_type',

ConnectionProperty('auth_scheme') AS 'auth_scheme',

ConnectionProperty('local_net_address') AS 'local_net_address',

ConnectionProperty('local_tcp_port') AS 'local_tcp_port',

ConnectionProperty('client_net_address') AS 'client_net_address',

ConnectionProperty('physical_net_transport') AS 'physical_net_transport'

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!

alex.sqldba SSChampion Points: 10254 More actions · Answer 2

Hi Lowell,

I very much like your trigger! I've saved that into my collection 🙂

My only concern is because it's modying an existing object, its going to need to go through the ridiculous and flawed change control panel, which means its a week away from being approved.

Are you then saying that with the Audit/ExEv I cannot get WhoWhatWhen information?

Cheers

Alex

Lowell SSC Guru Points: 323504 More actions · Answer 3

Sql audit can track who made changes in general, and when, but to the best of my knowledge cannot say what row changed, but not whether your statusflag column was involved.

I believe extended events can catch who, when and imply the what, but not whether it was rowid 42 or whatever. i think EE can filter on the update statement containing the string value of the column, so it might know if the column was involved. someone stronger in extended events might know better.

Change tracking and change data capture can capture the details of what changed and when, but not the who did it.

Afaik only a trigger can capture all three.

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!

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 4

You can also take a look at Change Data Capture. It can also provide you a complete change history on tables you identify. Like triggers it will be a change to your database as it add items for the tracking of changed data, but you might get a little better justification in that it is built-in SQL Server functionality specifically built for this purpose.

About Change Data Capture (SQL Server)

https://msdn.microsoft.com/en-us/library/cc645937(v=sql.110).aspx

Joie Andrew
"Since 1982"