1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
| 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 |
Partager