May 11, 2012 at 4:32 pm
I am trying to set up auditing dml activities within the database for logins with sysadmin privilege. For example, I want to track all dml activities within database 'ABC' for a login 'domain/sqllogin' which has sysadmin provilege. But, I don't want to turn on audit on 'dbo' because many production queries run as 'dbo'. Please help !
May 11, 2012 at 4:49 pm
In a database-level audit I think your options are limited. All sysadmins are mapped into the DB as dbo.
You could run a Trace and filter on the SessionLoginName. This is the "Original" login, meaning no matter what happens in the session in terms of context switching you can always see the person who started the session. I suspect you would also need to filter on TextData looking for INSERT, UPDATE, DELETE, TRUNCATE, MERGE, etc.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2012 at 9:47 pm
Thanks, currently I implemented extended events. But, i'd like to see I can use SQL 2008R2 audit feature that is available in enterprise edition.
May 12, 2012 at 3:59 pm
injungchang (5/11/2012)
Thanks, currently I implemented extended events. But, i'd like to see I can use SQL 2008R2 audit feature that is available in enterprise edition.
I will recommend using EE in SQL 2008R2 for certain applications, but in general will recommend Trace on these forums mainly because EE is not a complete replacement for Trace. However, in SQL Server 2012 the EE team has added the necessary events to make it a complete replacement for Trace so I will generally recommend EE if on that version. Kudos for using EE instead of Trace. I think this is the direction Microsoft is recommending us to move.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 12, 2012 at 10:19 pm
I will stick to EE for now. Appreciate your insightful reply.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply