Securty Trigger stroed procedure

Question

Securty Trigger stroed procedure

srimkumarp-720356

Ten Centuries

Points: 1300
More actions
July 22, 2013 at 5:09 am

#280351

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.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

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>

<xs:element name="EventType" type="SSWNAMEType"/>

<xs:element name="PostTime" type="xs:string"/>

<xs:element name="SPID" type="xs:int"/>

<xs:element name="ServerName" type="PathType"/>

<xs:element name="LoginName" type="SSWNAMEType"/>

<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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

srimkumarp-720356 Ten Centuries Points: 1300 More actions · Answer 2

Thanks for the information.

We are using only Server level audit trigger.

For example :- I created the login on one server and later drop the same login. Now I want to capture the SQL command scripts and how did it with their login ids.

Here problem is for us Create login SQL script is not capturing using Server level trigger ,other Tasks I am able to.

This is mainly to find the audit levels on Sql server end.

Lowell SSC Guru Points: 323504 More actions · Answer 3

srimkumarp-720356 (7/22/2013)
Thanks for the information.
We are using only Server level audit trigger.
For example :- I created the login on one server and later drop the same login. Now I want to capture the SQL command scripts and how did it with their login ids.
Here problem is for us Create login SQL script is not capturing using Server level trigger ,other Tasks I am able to.
This is mainly to find the audit levels on Sql server end.

well, the absolute best way to get instant help is to post your trigger definition here; then we can see what you are refering to , and offer suggestions.

For example, what events is your server trigger capturing at this time? CREATE_LOGIN,ALTER_LOGIN,DROP_LOGIN?

do you have ,AUDIT_LOGIN_CHANGE_PROPERTY_EVENT,AUDIT_LOGIN_CHANGE_PASSWORD_EVENT,ADD_LOGIN_TO_SERVER_ROLE_EVENT?

Have you considered switching to Event Notifications, so you can capture other items with a lighter server footprint?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

srimkumarp-720356 Ten Centuries Points: 1300 More actions · Answer 4

In SP , we have implemented below events :-

DDL_LOGIN_EVENTS

, DDL_GDR_SERVER_EVENTS

, DDL_AUTHORIZATION_SERVER_EVENTS

, CREATE_DATABASE

, DROP_DATABASE

, ALTER_DATABASE

Even though DDL_LOGIN_EVENTS implemented in SP , we are not able to capture the Create login script in table.

I have attched the server trigger , please find attached sp and assist me.

srimkumarp-720356 Ten Centuries Points: 1300 More actions · Answer 5

Hi,

Below is the example code for DDL server level Trigger

declare @data xml,

@CommandText varchar(max)

set @data = eventdata()

set @CommandText=@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')

OUTPUT :-

FOR EVENTTYPE =DROP_LOGIN I am getting commandtext - DROP LOGIN [testing]

where as for CREATE_LOGIN and Alter_Login I am gettting Null

Could anyone of you assist me.

Thanks.

Lowell SSC Guru Points: 323504 More actions · Answer 6

ok i created this complete example by merging some of your stuff into my known, working trigger example.

it's working well for me, but has a couple of duplicate columns from what you were capturing; additionally, if you are using 2008 and above, you can see i'm capturing the IP addresses and stuff related to the connection(not available in 2005)

also note i gave the explicit path to the audit table, which yours would not , and could potentially fail if the user who is adding a user does nto have a default database set to master.

use master;

CREATE TABLE [master].[dbo].[DDLEVENTLOG] (

[SERVERNAME] VARCHAR(128) NULL,

[DATABASENAME] VARCHAR(128) NULL,

[EVENTDATE] DATETIME NULL DEFAULT (getdate()),

[SPID] INT NULL,

[LOGINNAME] VARCHAR(128) NULL,

[USERNAME] VARCHAR(128) NULL,

[SYSTEMUSER] VARCHAR(128) NULL,

[CURRENTUSER] VARCHAR(128) NULL,

[ORIGINALUSER] VARCHAR(128) NULL,

[HOSTNAME] VARCHAR(128) NULL,

[APPLICATIONNAME] VARCHAR(128) NULL,

[PROGRAMNAME] VARCHAR(128) NULL,

[NET_TRANSPORT] VARCHAR(128) NULL,

[PROTOCOL_TYPE] VARCHAR(128) NULL,

[AUTH_SCHEME] VARCHAR(128) NULL,

[SERVER_NET_ADDRESS] VARCHAR(128) NULL,

[SERVER_TCP_PORT] VARCHAR(128) NULL,

[CLIENT_IP_ADDRESS] VARCHAR(128) NULL,

[PHYSICAL_NET_TRANSPORT] VARCHAR(128) NULL,

[EVENTTYPE] VARCHAR(128) NULL,

[SCHEMANAME] VARCHAR(128) NULL,

[OBJECTNAME] VARCHAR(128) NULL,

[OBJECTTYPE] VARCHAR(128) NULL,

[EVENTDATA] XML NULL,

[COMMANDTEXT] VARCHAR(max) NULL);

--if everyone does not have access to this table, all non-sysadmins will fail in the login trigger.

GRANT INSERT ON [dbo].[DDLEVENTLOG] TO PUBLIC;

GO

CREATE TRIGGER [trg_audit]

ON ALL SERVER

FOR DDL_LOGIN_EVENTS

, DDL_GDR_SERVER_EVENTS

, DDL_AUTHORIZATION_SERVER_EVENTS

, CREATE_DATABASE

, DROP_DATABASE

, ALTER_DATABASE

AS

DECLARE @xmlEventData XML

SET @xmlEventData = eventdata()

INSERT INTO [master].[dbo].[DDLEVENTLOG]

(

SERVERNAME,

DATABASENAME,

EVENTDATE,

SPID,

LOGINNAME,

USERNAME,

SYSTEMUSER,

CURRENTUSER,

ORIGINALUSER,

HOSTNAME,

APPLICATIONNAME,

PROGRAMNAME,

NET_TRANSPORT,

PROTOCOL_TYPE,

AUTH_SCHEME,

SERVER_NET_ADDRESS,

SERVER_TCP_PORT,

CLIENT_IP_ADDRESS,

PHYSICAL_NET_TRANSPORT,

EVENTTYPE,

SCHEMANAME,

OBJECTNAME,

OBJECTTYPE,

[EVENTDATA],

COMMANDTEXT

)

SELECT

CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')) AS SERVERNAME,

CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')) AS DATABASENAME,

GETDATE() AS EVENTDATE,

CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/SPID)')) AS SPID,

CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')) AS LOGINNAME,

CONVERT(VARCHAR(100), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')) AS USERNAME,

SUSER_SNAME() AS SYSTEMUSER,

CURRENT_USER AS CURRENTUSER,

ORIGINAL_LOGIN() AS ORIGINALUSER,

HOST_NAME() AS HOSTNAME,

APP_NAME() AS APPLICATIONNAME,

PROGRAM_NAME() AS PROGRAMNAME,

CONVERT(VARCHAR(128),ConnectionProperty('net_transport')) AS NET_TRANSPORT,

CONVERT(VARCHAR(128),ConnectionProperty('protocol_type')) AS PROTOCOL_TYPE,

CONVERT(VARCHAR(128),ConnectionProperty('auth_scheme')) AS AUTH_SCHEME,

CONVERT(VARCHAR(128),ConnectionProperty('local_net_address')) AS SERVER_NET_ADDRESS,

CONVERT(VARCHAR(128),ConnectionProperty('local_tcp_port')) AS SERVER_TCP_PORT,

CONVERT(VARCHAR(128),ConnectionProperty('client_net_address')) AS CLIENT_IP_ADDRESS,

CONVERT(VARCHAR(128),ConnectionProperty('physical_net_transport')) AS PHYSICAL_NET_TRANSPORT,

CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')) AS EVENTTYPE,

CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/SchemaName)')) AS SCHEMANAME,

CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')) AS OBJECTNAME,

CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')) AS OBJECTTYPE,

@xmlEventData AS [EVENTDATA],

CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) AS COMMANDTEXT

GO

ENABLE TRIGGER [trg_audit] ON ALL SERVER

/*

Cleanup:

DISABLE TRIGGER [trg_audit] ON ALL SERVER

DROP TRIGGER [trg_audit] ON ALL SERVER

DROP TABLE [master].[dbo].[DDLEVENTLOG]

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

srimkumarp-720356 Ten Centuries Points: 1300 More actions · Answer 7

Thanks for your support Lowell.

I executed your script and tested. I found few issues and I have attached, can you please check. I am using SQL Server 2005.

thanks in advance.

Lowell SSC Guru Points: 323504 More actions · Answer 8

not sure if that's an issue or not. on my test server, i did exactly this code, and see the command text for both;

regardless of whether the command text is visible int he grid or not, the event does show up int eh DDL as CREATE LOGIN, and the object name is the name of the login too.

so i don't see an error, just a situation where you are nto seeing an expected value.what is it that is not working.

we saved the xml event data itself, right there in the audit table, so you can see what raw data we processed anyway.

select that xml value, copy it and paste it into notepad or something.is the command text empty, or does it have a bunch of Carriage returns or something so it's hiding it in the grid?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

srimkumarp-720356 Ten Centuries Points: 1300 More actions · Answer 9

Thank you for your support Lowell.

In SQL 2008 it is working fine . Seems tobe some issue in SQL 2005. I will look into this & let you know. Thanks.

srimkumarp-720356 Ten Centuries Points: 1300 More actions · Answer 10

Hi Lowell,

Using DDL Trigger script , I deployed in Sql 2008 server and found few DDL events not capturing.

Example:- TestA SQL server login I created and grant SA permissions , using that login I created TestB login.

This type of activites DDL triggers not loading into table. Can you please guide me , do I need to use any server objects like service broker or any messaging techniques to track all DDL activites.

Thanks ,Kumar.