srimkumarp-720356 (7/22/2013)
Hi ,I want to capturethe Audit Server Security Events , like if any one doing Create / Alter / Drop logins on server level , need to sent to SQL Table.
I can able to capture the Drop login and other DML operations but not able to perform for Create & Alter for logins.
Below command I am using for capturing the commands.
CommandText =
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
Is there any way to find the details:-
Need to modify security trigger with login updates and it need to collect alter/modify/delete login with permission details
Can you please assist me.
Thanks, Kumar.
so you have a database level or server level DDL Trigger that is capturing the events, right? did you look at the definition for events the Microsoft XML, and see which events are available?
for example the ALTER_LOGIN EVENT has this xml:
<xs:complexType name="EVENT_INSTANCE_ALTER_LOGIN">
<xs:sequence>
<!-- Basic Envelope -->
<xs:element name="EventType" type="SSWNAMEType"/>
<xs:element name="PostTime" type="xs:string"/>
<xs:element name="SPID" type="xs:int"/>
<!-- Server Scoped DDL -->
<xs:element name="ServerName" type="PathType"/>
<xs:element name="LoginName" type="SSWNAMEType"/>
<!-- Main Body -->
<xs:element name="ObjectName" type="SSWNAMEType"/>
<xs:element name="ObjectType" type="SSWNAMEType"/>
<xs:element name="DefaultLanguage" type="SSWNAMEType"/>
<xs:element name="DefaultDatabase" type="SSWNAMEType"/>
<xs:element name="LoginType" type="SSWNAMEType"/>
<xs:element name="SID" type="SID"/>
<xs:element name="TSQLCommand" type="EventTag_TSQLCommand"/>
</xs:sequence>
</xs:complexType>
Lowell