Auditing using Triggers

Question

Post reply

Auditing using Triggers

Chimney

Old Hand

Points: 366
More actions
September 7, 2011 at 6:51 am

#257008

Hi,
Our client is using Triggers (in Sybase) for database auditing and we are following the same approach in SQL Server 2008 R2 as well.
Client was not happy with these features because of the following reasons.
CDC : Will create replica of the table to be audited with few more columns. The database size will grow if you want to audit all the tables in the DB.
SQL Server Auditing : This is not giving the column details of the added/Updated. Instead, it is giving the parameters list.
In SQL Server also trigger approach is working fine except for DELETE. Because, triggers refer the CreatedBy and ModifiedBy columns to identify the user for Insert and Update but that doesn’t allow recording the user who deletes a row. (All the tables have CreatedBy and ModifiedBy columns to keep track of the windows user who created/updated the row)
Please let us know how to get the Windows user who deletes a row.
I look forward to your comments

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

What are they doing in Sybase that is so magic that no more space is required for loging?

I'd track how you populate the createdby and updatedby columns and use the same logic for deleted by.

There are tons of ways to do this in SQL but they all get something slightly different so I can't give you the straight answer without knowing the real requirements.

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

se.saravanan (9/7/2011)
Please let us know how to get the Windows user who deletes a row.

Same way you get the windows user who has done insert, update or any other operation. Nothing at all special about delete.

ORIGINAL_LOGIN()

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

Chimney Old Hand Points: 366 More actions · Answer 3

I'd track how you populate the createdby and updatedby columns and use the same logic for deleted by.

Thanks for your reply. CreatedBy and UpdatedBY columns will be populated from the .NET application with the user name who logged into Windows OS (NOT the Windows Authentication User because we are using SQL Server authentication). To be specific, CreatedBy will be populated when a new row gets inserted and UpdatedBy will be populated when the row updated.

We have written 'After Insert/Update/delete' triggers for auditing. With CreatedBy/UpdatedBy we can get the Windows user name and log the user info. But during delete, the username will be saved in none of the columns. So, we can not get the user name from CreateBy and UpdatedBy columns. Is there any other way to get the user name who logged into the comp?

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

GilaMonster (9/7/2011)
se.saravanan (9/7/2011)
Please let us know how to get the Windows user who deletes a row.
Same way you get the windows user who has done insert, update or any other operation. Nothing at all special about delete.
ORIGINAL_LOGIN()

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

Chimney Old Hand Points: 366 More actions · Answer 5

ORIGINAL_LOGIN() will give the user name who logged into SQL Server. In our case SQL Server Authentication user. But I need the windows user name who logged into the computer and not SQL Server.

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

Into what computer?

SQL only knows what's connecting to it. If you're using an application account, then the app needs to do the auditing, not SQL.

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

@Chimney

Here is a thread that may help shed some light on your situation:

http://www.sqlservercentral.com/Forums/Topic1097586-359-1.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Dave23 Hall of Fame Points: 3903 More actions · Answer 8

I don't know if it would make it a viable option for you, but with regard to SQL Server Auditing, there is an update available that will cause the actual values to be recorded in the audit record rather than just the parameters. I ran into this issue myself previously. Refer to http://support.microsoft.com/kb/967552.