1

I need to log events of admin changing someone's password, and user changing his password. How do I do that? I've found a nice solution based on Service Broker, however the resulting table only contains the login of account that made the change, not the affected account.
Could anyone tell me how to alter this part

INSERT dbo.PasswordChangeLog(LoginName) 
SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');

So that I'd insert the object of password change, too? I know I will have to ALTER dbo.PasswordChangeLog to add an extra column, of course.

2

You can extract much more data from the event instance in the Service Broker message to store in the table. Specifically, TargetLoginName should bring back the affected user for you, something like this should work:

SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') AS [ActioningLogin],
  @message_body.value('(/EVENT_INSTANCE/TargetLoginName)[1]', 'sysname') AS [AffectedLogin]

More info here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.