July 9, 2012 at 12:23 pm
I'd like to audit any updates to a database which use a specific SQL Server login, but do not originate from the application servers that should be the only connections using that login. Is it possible to create a database audit specification that would use the host_name() function to exclude activity from the authorized servers?
July 9, 2012 at 12:38 pm
Are you using triggers for the audit, or something else?
If you are using triggers, can you add "IF HOST_NAME() != 'MyWebServer' RETURN;" at the beggining of the trigger? (With, of course, the right value instead of MyWebServer, possibly a list and a Not In statement instead of a not-equals statement.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2012 at 3:10 pm
I hoped to use the built-in auditing feature of SQL Server 2008 Enterprise, but I'm unfamiliar with it and don't know if it can do this. A trigger is certainly a viable option.
July 12, 2012 at 6:59 am
If you're talking about Change Data Capture (CDC), I believe that's all/nothing on a table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 12, 2012 at 3:19 pm
The auditing feature captures events asynchronously, outside the context of the session, and therefore wouldn't capture the hostname (or the network login) for events like DML.
To capture the hostname, you'll need something like DML triggers, like GSquared mentioned.
Or you can use SQL Trace.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply