April 7, 2009 at 2:48 am
G'day,
I'm currently using SQL 2005. I'm trying to find a way of logging any additions to the sysadmin role. The only way I can think of doing it is either with a DDL trigger (then I will find out straight away), or by having a nightly job which emails me the list of sysadmins.
I know how to do the second, but ideally I'd like to do it with a trigger. I tried to use DDL_SERVER_SECURITY_EVENTS
i.e.
CREATE TRIGGER tr_serverChange
ON ALL SERVER
FOR DDL_SERVER_SECURITY_EVENTS
AS
BEGIN
-- log event here
END
And I receive:
Msg 1082, Level 15, State 1, Procedure tr_serverChange, Line 9
"DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.
This sounds like a bug to me, and google doesn't come up with any decent solutions. I've tried ADD_ROLE_MEMBER, but this seems to be SQL 2008 only.
I realise that the only people who could add someone to the sysadmin role are sysadmins themselves, and that they could just as well disable this trigger, but I'm just looking for
a way of monitoring whats going on out there in the background.
Does anyone know a solution to this problem, or failing that, another way of doing it that doesn't involved nightly jobs etc.
cheers,
April 7, 2009 at 3:12 am
It's not a bug, maybe a limitation. The event you mention cannot be used with DDL triggers. It can (iirc) be used with event notifications, so that may be a way for you to consider.
I haven't worked much with Event notifications, so all I can suggest is that you check books online on the subject.
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
April 7, 2009 at 3:27 am
thanks - I think event notifications are a Service Broker thing, and I haven't had a chance to look at that yet.
Think that'll be my next port of call.
I presumed that the DDL_DATABASE_LEVEL_EVENTS error was a bug, as I would've thought that adding/removing users to a role would be a fairly natural auditing requirement, but never mind, as along as I can find some way of doing it.
cheers,
April 7, 2009 at 3:33 am
wmt (4/7/2009)
I presumed that the DDL_DATABASE_LEVEL_EVENTS error was a bug, as I would've thought that adding/removing users to a role would be a fairly natural auditing requirement
Well, it is documented that adding and removing roles aren't events that DDL triggers can see, which makes it a stupid design decision rather than a bug. Can't recall where, I ran across it when trying to do much the same thing.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply