Trigger to audit permission changes in SQL Server 2005 on any object in any database

Create a table to maintain the records in master database because its a server level trigger:

===========================================================

create table dbo.PermissionAudit (eventtype nvarchar (100), serverLogin nvarchar(100) not null,DBUser nvarchar (100) not null,TSQLText varchar (max), eventdata xml not null)

Trigger Definition

===================== 

Create TRIGGER PermissionAudit

ON All Server

FOR

CREATE_LOGIN,ALTER_LOGIN, DROP_LOGIN, ALTER_AUTHORIZATION_SERVER,GRANT_SERVER,DENY_SERVER, REVOKE_SERVER,

AS

      DECLARE @Eventdata XML

      SET @Eventdata = EVENTDATA()

      INSERT into

            dbo.PermissionAudit

(EventType,EventData, ServerLogin,DBUser,TSQLText)

            VALUES

            (     @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

                  @Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),

                  @Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')

            )

Enabling the Trigger

======================

enable trigger PermissionAudit on all server

--Abhishek Soni