Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Securty Trigger stroed procedure Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 5:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 2:39 PM
Points: 64, Visits: 513
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.
Post #1475943
Posted Monday, July 22, 2013 5:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1475953
Posted Monday, July 22, 2013 6:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 2:39 PM
Points: 64, Visits: 513
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.
Post #1475975
Posted Monday, July 22, 2013 6:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1475978
Posted Monday, July 22, 2013 6:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 2:39 PM
Points: 64, Visits: 513

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.


  Post Attachments 
trg_audit.txt (6 views, 1.53 KB)
Post #1475992
Posted Wednesday, July 24, 2013 5:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 2:39 PM
Points: 64, Visits: 513
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.
Post #1477002
Posted Wednesday, July 24, 2013 6:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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;
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1477035
Posted Wednesday, July 31, 2013 7:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 2:39 PM
Points: 64, Visits: 513
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.


  Post Attachments 
error.bmp (2 views, 3.42 MB)
Post #1479493
Posted Wednesday, July 31, 2013 8:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1479518
Posted Thursday, August 1, 2013 1:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 2:39 PM
Points: 64, Visits: 513

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.
Post #1479809
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse