audit sysadmin role

Question

audit sysadmin role

mobasha

SSCertifiable

Points: 5334
More actions
April 27, 2008 at 12:58 am

#189820

hi.
can i some how, be notified if a user has been added to the sysadmin server role?
am trying to create this trigger:
CREATE TRIGGER ddl_trig_login
ON ALL SERVER
FOR DDL_USER_EVENTS
AS
PRINT 'Login Event Issued.'
GO
but i get an error msg
Msg 1098, Level 15, State 1, Procedure ddl_trig_login, Line 5
The specified event type(s) is/are not valid on the specified target object.
and i dont know why when i replace DDL_USER_EVENTS with DDL_LOGIN_EVENTS
every thing works fine??
..>>..
MobashA

Viewing 5 posts - 1 through 4 (of 4 total)

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

thecosmictrickster@gmail.com SSChampion Points: 10386 More actions · Answer 1

I've got a trigger setup on ALTER LOGIN events. I'm using Database Mail to send me an email notification. Here's the code:

[font="Courier New"]CREATE TRIGGER [tddl_alterloginnotification]

ON ALL SERVER

FOR ALTER_LOGIN

AS

DECLARE @data xml

DECLARE @MailBody nvarchar(4000)

SET @data = EVENTDATA()

SET @MailBody = 'Login Name: ' + @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)') + CHAR(13)

SET @MailBody = @MailBody + 'T-SQL: ' + @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'EmailProfile',

@recipients = 'my.email@the.company',

@subject = 'Login altered on MyServer',

@body = @MailBody

GO

ENABLE TRIGGER [tddl_alterloginnotification] ON ALL SERVER

GO

[/font]

The body of the message contains the actual code executed, so you would see what was altered on the login. It catches ALL modifications, so may be overkill for your case. It depends how often your logins get altered. You could probably put some filter code in the stuff above so it only alerts if the @MailBody variable contains 'sysadmin'.

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

hi

You will have to check for newly created logins. Check for the create login event also.

"Keep Trying"

mobasha SSCertifiable Points: 5334 More actions · Answer 3

this one is good, i think i could add some code to rollback the alter if the role was sysadmin, i think it might do the work.

thanks

..>>..

MobashA

mobasha SSCertifiable Points: 5334 More actions · Answer 4

am trying to use the code but the TSQLCommand is returning NULL??

CREATE TRIGGER ddl_trig_login

ON ALL SERVER

FOR Create_login

AS

PRINT 'Login Event Issued.'

SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')

ROLLBACK;

GO

CREATE LOGIN [test] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

..>>..

MobashA