articles

Articles of SQLschool.gr Team

Storing DDL Statements History

Antonios Chatzipavlis

Friday 27 November 2015

Manage / Monitor / TuneSecurity / Auditing

DDL triggerDDL HistoryAuditingDBASecuritySQL Server 2005SQL Server 2008SQL Server 2008R2SQL Server 2012SQL Server 2014SQL Server 2016Tips & Tricks

Ένας οργανισμός έχει πολλά SQL Server Instances, πολλές databases και σίγουρα πάνω από έναν DBA, DB developer που έχουν πρόσβαση σε αυτά τα instances και αυτές τις databases.

Ζητούμενο από όλους είναι να γνωρίζουμε τις αλλαγές που έχουν γίνει σε αυτές τις databases σε επίπεδο schema αλλά και πότε μπήκε ένας χρήστης σε ποιο ρόλο και πολλά ακόμα στοιχεία όπως δημιουργία indexes, αλλαγή στο μέγεθος μιας βάσης κλπ.

Όλα αυτά ακόμα και μέσα από το SSMS να γίνουν είναι Data Definition Language (DDL) statements και χρήσιμο είναι να μπορούν να καταγραφούν ώστε να υπάρχει ένα ιστορικό για τις αλλαγές αυτές.

Στο SQL Server υπάρχουν μηχανισμοί Auditing που κάνουν εξαιρετική δουλειά. Βέβαια στο παρελθόν κάτι τέτοιο υπήρχε σαν δυνατότητα μόνο στις Enterprise εκδόσεις.

Σε αυτό το άρθρο όμως δεν θα μιλήσουμε για αυτούς τους μηχανισμούς αλλά για έναν customize μηχανισμό που μπορεί να χρησιμοποιηθεί σε όλες τις εκδόσεις (Std, Ent) και μου μπορεί κάποιος να προσθέσει περισσότερη custom πληροφορία.

DDL Trigger

Από την έκδοση του SQL Server 2005 υπάρχουν οι DDL Triggers που μπορούν να χρησιμοποιηθούν εφόσον θέλουμε να κάνουμε κάτι παραπάνω σε κάποιο από τα DDL statements (CREATE, ALTER, DROP).

Οι triggers αυτοί έχουν scope είτε όλο το instance είτε συγκεκριμένη database και εκτελούνται όταν κάποιο DDL statement εκτελεστεί. Αυτά αναγράφονται στο πίνακα που υπάρχει μέσα στο άρθρο αυτό στο msdn και που για την ευκολία στην ανάγνωση παραθέτω και εδώ.

parent_type	type	name
NULL	296	ALTER_SERVER_CONFIGURATION
NULL	10001	DDL_EVENTS
10001	10016	\| DDL_DATABASE_LEVEL_EVENTS
10016	10027	\| \| DDL_ASSEMBLY_EVENTS
10027	102	\| \| \| ALTER_ASSEMBLY
10027	101	\| \| \| CREATE_ASSEMBLY
10027	103	\| \| \| DROP_ASSEMBLY
10016	10029	\| \| DDL_DATABASE_SECURITY_EVENTS
10029	10033	\| \| \| DDL_APPLICATION_ROLE_EVENTS
10033	138	\| \| \| \| ALTER_APPLICATION_ROLE
10033	137	\| \| \| \| CREATE_APPLICATION_ROLE
10033	139	\| \| \| \| DROP_APPLICATION_ROLE
10029	10038	\| \| \| DDL_ASYMMETRIC_KEY_EVENTS
10038	248	\| \| \| \| ALTER_ASYMMETRIC_KEY
10038	247	\| \| \| \| CREATE_ASYMMETRIC_KEY
10038	249	\| \| \| \| DROP_ASYMMETRIC_KEY
10029	10036	\| \| \| DDL_AUTHORIZATION_DATABASE_EVENTS
10036	205	\| \| \| \| ALTER_AUTHORIZATION_DATABASE
10029	10030	\| \| \| DDL_CERTIFICATE_EVENTS
10030	198	\| \| \| \| ALTER_CERTIFICATE
10030	197	\| \| \| \| CREATE_CERTIFICATE
10030	199	\| \| \| \| DROP_CERTIFICATE
10029	10039	\| \| \| DDL_CRYPTO_SIGNATURE_EVENTS
10039	257	\| \| \| \| ADD_SIGNATURE
10039	255	\| \| \| \| ADD_SIGNATURE_SCHEMA_OBJECT
10039	258	\| \| \| \| DROP_SIGNATURE
10039	256	\| \| \| \| DROP_SIGNATURE_SCHEMA_OBJECT
10029	10066	\| \| \| DDL_DATABASE_AUDIT_SPECIFICATION_EVENTS
10066	291	\| \| \| \| ALTER_DATABASE_AUDIT_SPECIFICATION
10066	290	\| \| \| \| CREATE_DATABASE_AUDIT_SPECIFICATION
10066	292	\| \| \| \| DROP_DATABASE_AUDIT_SPECIFICATION
10029	10062	\| \| \| DDL_DATABASE_ENCRYPTION_KEY_EVENTS
10062	279	\| \| \| \| ALTER_DATABASE_ENCRYPTION_KEY
10062	278	\| \| \| \| CREATE_DATABASE_ENCRYPTION_KEY
10062	280	\| \| \| \| DROP_DATABASE_ENCRYPTION_KEY
10029	10035	\| \| \| DDL_GDR_DATABASE_EVENTS
10035	171	\| \| \| \| DENY_DATABASE
10035	170	\| \| \| \| GRANT_DATABASE
10035	172	\| \| \| \| REVOKE_DATABASE
10029	10040	\| \| \| DDL_MASTER_KEY_EVENTS
10040	253	\| \| \| \| ALTER_MASTER_KEY
10040	252	\| \| \| \| CREATE_MASTER_KEY
10040	254	\| \| \| \| DROP_MASTER_KEY
10029	10032	\| \| \| DDL_ROLE_EVENTS
10032	207	\| \| \| \| ADD_ROLE_MEMBER
10032	135	\| \| \| \| ALTER_ROLE
10032	134	\| \| \| \| CREATE_ROLE
10032	136	\| \| \| \| DROP_ROLE
10032	208	\| \| \| \| DROP_ROLE_MEMBER
10029	10034	\| \| \| DDL_SCHEMA_EVENTS
10034	142	\| \| \| \| ALTER_SCHEMA
10034	141	\| \| \| \| CREATE_SCHEMA
10034	143	\| \| \| \| DROP_SCHEMA
10029	10037	\| \| \| DDL_SYMMETRIC_KEY_EVENTS
10037	245	\| \| \| \| ALTER_SYMMETRIC_KEY
10037	244	\| \| \| \| CREATE_SYMMETRIC_KEY
10037	246	\| \| \| \| DROP_SYMMETRIC_KEY
10029	10031	\| \| \| DDL_USER_EVENTS
10031	132	\| \| \| \| ALTER_USER
10031	131	\| \| \| \| CREATE_USER
10031	133	\| \| \| \| DROP_USER
10016	10052	\| \| DDL_DEFAULT_EVENTS
10052	218	\| \| \| BIND_DEFAULT
10052	220	\| \| \| CREATE_DEFAULT
10052	231	\| \| \| DROP_DEFAULT
10052	242	\| \| \| UNBIND_DEFAULT
10016	10026	\| \| DDL_EVENT_NOTIFICATION_EVENTS
10026	74	\| \| \| CREATE_EVENT_NOTIFICATION
10026	76	\| \| \| DROP_EVENT_NOTIFICATION
10016	10053	\| \| DDL_EXTENDED_PROPERTY_EVENTS
10053	211	\| \| \| ALTER_EXTENDED_PROPERTY
10053	222	\| \| \| CREATE_EXTENDED_PROPERTY
10053	233	\| \| \| DROP_EXTENDED_PROPERTY
10016	10054	\| \| DDL_FULLTEXT_CATALOG_EVENTS
10054	212	\| \| \| ALTER_FULLTEXT_CATALOG
10054	223	\| \| \| CREATE_FULLTEXT_CATALOG
10054	234	\| \| \| DROP_FULLTEXT_CATALOG
10016	10067	\| \| DDL_FULLTEXT_STOPLIST_EVENTS
10067	294	\| \| \| ALTER_FULLTEXT_STOPLIST
10067	293	\| \| \| CREATE_FULLTEXT_STOPLIST
10067	295	\| \| \| DROP_FULLTEXT_STOPLIST
10016	10023	\| \| DDL_FUNCTION_EVENTS
10023	62	\| \| \| ALTER_FUNCTION
10023	61	\| \| \| CREATE_FUNCTION
10023	63	\| \| \| DROP_FUNCTION
10016	10049	\| \| DDL_PARTITION_EVENTS
10049	10050	\| \| \| DDL_PARTITION_FUNCTION_EVENTS
10050	192	\| \| \| \| ALTER_PARTITION_FUNCTION
10050	191	\| \| \| \| CREATE_PARTITION_FUNCTION
10050	193	\| \| \| \| DROP_PARTITION_FUNCTION
10049	10051	\| \| \| DDL_PARTITION_SCHEME_EVENTS
10051	195	\| \| \| \| ALTER_PARTITION_SCHEME
10051	194	\| \| \| \| CREATE_PARTITION_SCHEME
10051	196	\| \| \| \| DROP_PARTITION_SCHEME
10016	10055	\| \| DDL_PLAN_GUIDE_EVENTS
10055	216	\| \| \| ALTER_PLAN_GUIDE
10055	228	\| \| \| CREATE_PLAN_GUIDE
10055	238	\| \| \| DROP_PLAN_GUIDE
10016	10024	\| \| DDL_PROCEDURE_EVENTS
10024	52	\| \| \| ALTER_PROCEDURE
10024	51	\| \| \| CREATE_PROCEDURE
10024	53	\| \| \| DROP_PROCEDURE
10016	10056	\| \| DDL_RULE_EVENTS
10056	219	\| \| \| BIND_RULE
10056	229	\| \| \| CREATE_RULE
10056	239	\| \| \| DROP_RULE
10056	243	\| \| \| UNBIND_RULE
10016	10069	\| \| DDL_SEARCH_PROPERTY_LIST_EVENTS
10069	298	\| \| \| ALTER_SEARCH_PROPERTY_LIST
10069	297	\| \| \| CREATE_SEARCH_PROPERTY_LIST
10069	299	\| \| \| DROP_SEARCH_PROPERTY_LIST
10016	10070	\| \| DDL_SEQUENCE_EVENTS
10070	304	\| \| \| ALTER_SEQUENCE
10070	303	\| \| \| CREATE_SEQUENCE
10070	305	\| \| \| DROP_SEQUENCE
10016	10041	\| \| DDL_SSB_EVENTS
10041	10063	\| \| \| DDL_BROKER_PRIORITY_EVENTS
10063	282	\| \| \| \| ALTER_BROKER_PRIORITY
10063	281	\| \| \| \| CREATE_BROKER_PRIORITY
10063	283	\| \| \| \| DROP_BROKER_PRIORITY
10041	10043	\| \| \| DDL_CONTRACT_EVENTS
10043	154	\| \| \| \| CREATE_CONTRACT
10043	156	\| \| \| \| DROP_CONTRACT
10041	10042	\| \| \| DDL_MESSAGE_TYPE_EVENTS
10042	152	\| \| \| \| ALTER_MESSAGE_TYPE
10042	151	\| \| \| \| CREATE_MESSAGE_TYPE
10042	153	\| \| \| \| DROP_MESSAGE_TYPE
10041	10044	\| \| \| DDL_QUEUE_EVENTS
10044	158	\| \| \| \| ALTER_QUEUE
10044	157	\| \| \| \| CREATE_QUEUE
10044	159	\| \| \| \| DROP_QUEUE
10041	10047	\| \| \| DDL_REMOTE_SERVICE_BINDING_EVENTS
10047	175	\| \| \| \| ALTER_REMOTE_SERVICE_BINDING
10047	174	\| \| \| \| CREATE_REMOTE_SERVICE_BINDING
10047	176	\| \| \| \| DROP_REMOTE_SERVICE_BINDING
10041	10046	\| \| \| DDL_ROUTE_EVENTS
10046	165	\| \| \| \| ALTER_ROUTE
10046	164	\| \| \| \| CREATE_ROUTE
10046	166	\| \| \| \| DROP_ROUTE
10041	10045	\| \| \| DDL_SERVICE_EVENTS
10045	162	\| \| \| \| ALTER_SERVICE
10045	161	\| \| \| \| CREATE_SERVICE
10045	163	\| \| \| \| DROP_SERVICE
10016	10022	\| \| DDL_SYNONYM_EVENTS
10022	34	\| \| \| CREATE_SYNONYM
10022	36	\| \| \| DROP_SYNONYM
10016	10017	\| \| DDL_TABLE_VIEW_EVENTS
10017	10020	\| \| \| DDL_INDEX_EVENTS
10020	213	\| \| \| \| ALTER_FULLTEXT_INDEX
10020	25	\| \| \| \| ALTER_INDEX
10020	224	\| \| \| \| CREATE_FULLTEXT_INDEX
10020	24	\| \| \| \| CREATE_INDEX
10020	274	\| \| \| \| CREATE_SPATIAL_INDEX
10020	206	\| \| \| \| CREATE_XML_INDEX
10020	235	\| \| \| \| DROP_FULLTEXT_INDEX
10020	26	\| \| \| \| DROP_INDEX
10017	10021	\| \| \| DDL_STATISTICS_EVENTS
10021	27	\| \| \| \| CREATE_STATISTICS
10021	29	\| \| \| \| DROP_STATISTICS
10021	28	\| \| \| \| UPDATE_STATISTICS
10017	10018	\| \| \| DDL_TABLE_EVENTS
10018	22	\| \| \| \| ALTER_TABLE
10018	21	\| \| \| \| CREATE_TABLE
10018	23	\| \| \| \| DROP_TABLE
10017	10019	\| \| \| DDL_VIEW_EVENTS
10019	42	\| \| \| \| ALTER_VIEW
10019	41	\| \| \| \| CREATE_VIEW
10019	43	\| \| \| \| DROP_VIEW
10016	10025	\| \| DDL_TRIGGER_EVENTS
10025	72	\| \| \| ALTER_TRIGGER
10025	71	\| \| \| CREATE_TRIGGER
10025	73	\| \| \| DROP_TRIGGER
10016	10028	\| \| DDL_TYPE_EVENTS
10028	91	\| \| \| CREATE_TYPE
10028	93	\| \| \| DROP_TYPE
10016	10048	\| \| DDL_XML_SCHEMA_COLLECTION_EVENTS
10048	178	\| \| \| ALTER_XML_SCHEMA_COLLECTION
10048	177	\| \| \| CREATE_XML_SCHEMA_COLLECTION
10048	179	\| \| \| DROP_XML_SCHEMA_COLLECTION
10016	241	\| \| RENAME
10001	10002	\| DDL_SERVER_LEVEL_EVENTS
10002	214	\| \| ALTER_INSTANCE
10002	10071	\| \| DDL_AVAILABILITY_GROUP_EVENTS
10071	307	\| \| \| ALTER_AVAILABILITY_GROUP
10071	306	\| \| \| CREATE_AVAILABILITY_GROUP
10071	308	\| \| \| DROP_AVAILABILITY_GROUP
10002	10004	\| \| DDL_DATABASE_EVENTS
10004	202	\| \| \| ALTER_DATABASE
10004	201	\| \| \| CREATE_DATABASE
10004	203	\| \| \| DROP_DATABASE
10002	10003	\| \| DDL_ENDPOINT_EVENTS
10003	182	\| \| \| ALTER_ENDPOINT
10003	181	\| \| \| CREATE_ENDPOINT
10003	183	\| \| \| DROP_ENDPOINT
10002	10057	\| \| DDL_EVENT_SESSION_EVENTS
10057	265	\| \| \| ALTER_EVENT_SESSION
10057	264	\| \| \| CREATE_EVENT_SESSION
10057	266	\| \| \| DROP_EVENT_SESSION
10002	10011	\| \| DDL_EXTENDED_PROCEDURE_EVENTS
10011	221	\| \| \| CREATE_EXTENDED_PROCEDURE
10011	232	\| \| \| DROP_EXTENDED_PROCEDURE
10002	10012	\| \| DDL_LINKED_SERVER_EVENTS
10012	263	\| \| \| ALTER_LINKED_SERVER
10012	225	\| \| \| CREATE_LINKED_SERVER
10012	10013	\| \| \| DDL_LINKED_SERVER_LOGIN_EVENTS
10013	226	\| \| \| \| CREATE_LINKED_SERVER_LOGIN
10013	236	\| \| \| \| DROP_LINKED_SERVER_LOGIN
10012	262	\| \| \| DROP_LINKED_SERVER
10002	10014	\| \| DDL_MESSAGE_EVENTS
10014	215	\| \| \| ALTER_MESSAGE
10014	227	\| \| \| CREATE_MESSAGE
10014	237	\| \| \| DROP_MESSAGE
10002	10015	\| \| DDL_REMOTE_SERVER_EVENTS
10015	217	\| \| \| ALTER_REMOTE_SERVER
10015	230	\| \| \| CREATE_REMOTE_SERVER
10015	240	\| \| \| DROP_REMOTE_SERVER
10002	10058	\| \| DDL_RESOURCE_GOVERNOR_EVENTS
10058	273	\| \| \| ALTER_RESOURCE_GOVERNOR_CONFIG
10058	10059	\| \| \| DDL_RESOURCE_POOL
10059	268	\| \| \| \| ALTER_RESOURCE_POOL
10059	267	\| \| \| \| CREATE_RESOURCE_POOL
10059	269	\| \| \| \| DROP_RESOURCE_POOL
10058	10060	\| \| \| DDL_WORKLOAD_GROUP
10060	271	\| \| \| \| ALTER_WORKLOAD_GROUP
10060	270	\| \| \| \| CREATE_WORKLOAD_GROUP
10060	272	\| \| \| \| DROP_WORKLOAD_GROUP
10002	10005	\| \| DDL_SERVER_SECURITY_EVENTS
10005	209	\| \| \| ADD_SERVER_ROLE_MEMBER
10005	301	\| \| \| ALTER_SERVER_ROLE
10005	300	\| \| \| CREATE_SERVER_ROLE
10005	10008	\| \| \| DDL_AUTHORIZATION_SERVER_EVENTS
10008	204	\| \| \| \| ALTER_AUTHORIZATION_SERVER
10005	10009	\| \| \| DDL_CREDENTIAL_EVENTS
10009	260	\| \| \| \| ALTER_CREDENTIAL
10009	259	\| \| \| \| CREATE_CREDENTIAL
10009	261	\| \| \| \| DROP_CREDENTIAL
10005	10061	\| \| \| DDL_CRYPTOGRAPHIC_PROVIDER_EVENTS
10061	276	\| \| \| \| ALTER_CRYPTOGRAPHIC_PROVIDER
10061	275	\| \| \| \| CREATE_CRYPTOGRAPHIC_PROVIDER
10061	277	\| \| \| \| DROP_CRYPTOGRAPHIC_PROVIDER
10005	10007	\| \| \| DDL_GDR_SERVER_EVENTS
10007	168	\| \| \| \| DENY_SERVER
10007	167	\| \| \| \| GRANT_SERVER
10007	169	\| \| \| \| REVOKE_SERVER
10005	10006	\| \| \| DDL_LOGIN_EVENTS
10006	145	\| \| \| \| ALTER_LOGIN
10006	144	\| \| \| \| CREATE_LOGIN
10006	146	\| \| \| \| DROP_LOGIN
10005	10064	\| \| \| DDL_SERVER_AUDIT_EVENTS
10064	285	\| \| \| \| ALTER_SERVER_AUDIT
10064	284	\| \| \| \| CREATE_SERVER_AUDIT
10064	286	\| \| \| \| DROP_SERVER_AUDIT
10005	10065	\| \| \| DDL_SERVER_AUDIT_SPECIFICATION_EVENTS
10065	288	\| \| \| \| ALTER_SERVER_AUDIT_SPECIFICATION
10065	287	\| \| \| \| CREATE_SERVER_AUDIT_SPECIFICATION
10065	289	\| \| \| \| DROP_SERVER_AUDIT_SPECIFICATION
10005	10010	\| \| \| DDL_SERVICE_MASTER_KEY_EVENTS
10010	251	\| \| \| \| ALTER_SERVICE_MASTER_KEY
10005	302	\| \| \| DROP_SERVER_ROLE
10005	210	\| \| \| DROP_SERVER_ROLE_MEMBER

Ο καθένας μπορεί ανάλογα με τις ανάγκες του να εκμεταλλευτεί τις δυνατότητες των DDL triggers σεβόμενος το performance στο instance. Και επειδή αρκετοί θα πούνε ότι οι DDL triggers έχουν κόστος στην απόδοση θα πρέπει να πως ξεκάθαρα ότι το κόστος είναι σχετικό με το τι έχουμε γράψει μέσα στον trigger. Άρα όταν ο κώδικας μας έχει γίνει tune τότε δεν υπάρχει λόγος ανησυχίας.

Custom Solution

Για την υλοποίηση του σεναρίου πρέπει να δημιουργηθεί μια database στην οποία θα δημιουργηθεί ένας πίνακας που να κρατάει την πληροφορία που χρειαζόμαστε, όπως παρακάτω
Σημ. Ο πίνακας αυτός έχει σχεδιαστεί με βάση τις ανάγκες που προσωπικά έχω

create database AdminLog;
go
use AdminLog;
go


create table DDLEvents
(
    id bigint identity(1,1),
    ActivityDate datetime2(7) default (sysdatetime()),
    EventType nvarchar(100),
    LoginName nvarchar(100),
    DatabaseName nvarchar(256),
    ObjectName nvarchar(256),
    ObjectType nvarchar(256),
    TargetObjectType nvarchar(100),
    TargetObjectName nvarchar(256),
    net_transport nvarchar(40),
    protocol_type nvarchar(40),
    encrypt_option nvarchar(40),
    auth_scheme nvarchar(40),
    client_net_address varchar(48),
    client_tcp_port int,
     [Eventdata] xml
)
go

Από εκεί και μετά και εφόσον θέλω να κρατάω πληροφορίες για όλες τις databases που έχω στο instance μου μπορώ να δημιουργήσω ένα ddl trigger για όλα τα ddl events σε όλο το instance (server) και μέσα σε αυτό απλά να κάνω καταγραφή του ddl event του οποίου τα στοιχεία μπορώ να καταναλώσω μέσα στον trigger με την χρήση της EVENTDATA() function. Επειδή θέλω να κρατήσω περισσότερες πληροφορίες για τον client που εκτέλεσε το συγκεκριμένο DDL statement διαβάζω την πληροφορία αυτή από το DMV sys.dm_exec_connections.
Αυτό όμως χρειάζεται για να εκτελεστεί ο user να έχει VIEW SERVER STATE permission και για αυτό το λόγο κάνω execute as τον trigger με τον sa (δεν χρειάζεται να είναι ενεργοποιημένος ούτε χρειάζεται να είναι το instance σε mixed mode).

use master;
go

create trigger [AllServerDDLEvents] on all server 
with execute as 'sa'
for DDL_EVENTS
as
begin
    set nocount on;

    declare @EventType nvarchar(100),
                @LoginName nvarchar(100),
                @DatabaseName nvarchar(256),
                @ObjectName nvarchar(256),
                @ObjectType nvarchar(256),
                @TargetObjectType nvarchar(100),
                @TargetObjectName nvarchar(256),
                @net_transport nvarchar(40),
                @protocol_type nvarchar(40),
                @encrypt_option nvarchar(40),
                @auth_scheme nvarchar(40),
                @client_net_address varchar(48),
                @client_tcp_port int,
                @SPID int

    declare  @data XML;
    set @data = EVENTDATA();
    set @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)');
    set @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)');
    set @DatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(256)');
    set @ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)');
    set @ObjectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(256)');
    set @TargetObjectType = @data.value('(/EVENT_INSTANCE/TargetObjectType)[1]', 'nvarchar(100)');
    set @TargetObjectName = @data.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'nvarchar(256)');
    set @SPID = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int');

    select    @net_transport = net_transport, 
                @protocol_type = protocol_type, 
                @encrypt_option = encrypt_option, 
                @auth_scheme = auth_scheme,     
                @client_net_address = client_net_address, 
                @client_tcp_port = client_tcp_port
    from sys.dm_exec_connections
    where session_id = @SPID;

    insert into AdminLog.dbo.DDLEvents
                                    (    EventType, 
                                        LoginName, 
                                        DatabaseName, 
                                        ObjectName, 
                                        ObjectType, 
                                        TargetObjectType, 
                                        TargetObjectName, 
                                        net_transport, 
                                        protocol_type, 
                                        encrypt_option, 
                                        auth_scheme, 
                                        client_net_address, 
                                        client_tcp_port, [Eventdata])
                values (    @EventType, 
                            @LoginName,    
                            @DatabaseName,
                            @ObjectName,
                            @ObjectType,
                            @TargetObjectType,
                            @TargetObjectName,
                            @net_transport,
                            @protocol_type,
                            @encrypt_option,
                            @auth_scheme,
                            @client_net_address,
                            @client_tcp_port,
                            @data);
end
go

Από το σημείο αυτό ότι DDL statement και να εκτελεστεί καταγράφεται στο table DDLEvents που είναι στην AdminLog database.
Σε αυτό τον πίνακα η πληροφορία που επιστρέφεται από την EVENTDATA και η οποία είναι σε XML format υπάρχει στο field του πίνακα eventdata.

Clearing old events

Επειδή οι ανάγκες του καθενός είναι διαφορετικές και άλλος μπορεί να θέλει αυτά τα δεδομένα να τα κρατήσει για συγκεκριμένο χρονικό διάστημα έχω φτιάξει ένα SQL Server Job με το οποίο σβήνω δεδομένα που είναι παλαιότερα των 14 μηνών και το job αυτό εκτελείται κάθε Σάββατο. Φυσικά αυτό μπορεί κανείς να το αλλάξει ώστε να ανταποκρίνεται στις ανάγκες του.

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 23/11/2015 2:44:08 μμ ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ClearDDLEvents', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [ClearEventsBiggerThanSixMonths]    Script Date: 23/11/2015 2:44:08 μμ ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ClearEventsBiggerThanSixMonths', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'delete from AdminLog.dbo.DDLEvents 
where [ActivityDate] <= DATEADD(month,-14,[ActivityDate]);', 
        @database_name=N'AdminLog', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DeleteDDLEventsSchedule', 
        @enabled=1, 
        @freq_type=8, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20151123, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959, 
        @schedule_uid=N'00000000-0000-0000-0000-000000000000'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Enjoy!
/*antonch*/

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.