December 27, 2011 at 11:57 am
How to audit security stored procedures in SQL Server 2005? E.g. I'd like to capture calls to sp_addrolemember/sp_droprolemember. I can create DDL trigger on ALTER_ROLE/CREATE_ROLE/DROP_ROLE events to capture sp_addrole/sp_droprole issued create role/drop role, but I am not aware of any event to capture add/drop role member ddl.
Thanks,
SY.
December 27, 2011 at 12:39 pm
ADD_ROLE_MEMBER and DROP_ROLE_MEMBER are capturable events at the server or database level, in DDL triggers.
- 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
December 27, 2011 at 12:47 pm
GSquared (12/27/2011)
ADD_ROLE_MEMBER and DROP_ROLE_MEMBER are capturable events at the server or database level, in DDL triggers.
I wish...
CREATE TRIGGER [AuditDDL_DDL_TRG]
ON DATABASE
FOR ADD_ROLE_MEMBER
AS
BEGIN
PRINT 'XXXXXXXXXXXXXXX'
END;
Msg 1082, Level 15, State 1, Procedure AuditDDL_DDL_TRG, Line 7
"ADD_ROLE_MEMBER" does not support synchronous trigger registration.
As stated in post title I am using SQL Server 2005, not 2008.
SY.
December 27, 2011 at 1:01 pm
Sorry. Forgot they added that one in 2008. And MSDN doesn't have the 2005 documentation on the DDL events available any more.
Looks like you're out of luck on 2005, in terms of triggers.
You should be able to capture those events in a trace, however. I think the default trace may even already do that, but I don't have a 2005 instance I can test it on right now.
- 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
December 27, 2011 at 2:27 pm
Thanks,
I ended up creating queue + notification service + event notification + SP with signature + queue activation.
SY.
December 28, 2011 at 6:27 am
solomon.yakobson (12/27/2011)
Thanks,I ended up creating queue + notification service + event notification + SP with signature + queue activation.
SY.
Good idea. Wouldn't have thought of that myself.
- 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply