tailieunhanh - SQL Server Tacklebox- P39

SQL Server Tacklebox- P39: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 7 Securing access to SQL Server SET @data EVENTDATA SET @tsqlCommand EVENTDATA .value EVENT_INSTANCE TSQLCommand CommandText 1 nvarchar max SET @eventType EVENTDATA .value EVENT_INSTANCE EventType 1 nvarchar ma x SET @serverName EVENTDATA .value EVENT_INSTANCE ServerName 1 nvarchar m ax SET @loginName EVENTDATA .value EVENT_INSTANCE LoginName 1 nvarchar ma x SET @userName EVENTDATA .value EVENT_INSTANCE UserName 1 nvarchar max SET @databaseName EVENTDATA .value EVENT_INSTANCE DatabaseName 1 nvarchar max SET @objectName EVENTDATA .value EVENT_INSTANCE ObjectName 1 nvarchar m ax SET @objectType EVENTDATA .value EVENT_INSTANCE ObjectType 1 nvarchar m ax SET @emailBody --------------------------------- CHAR 13 - DDL Trigger Activation Report - CHAR 13 --------------------------------------- CHAR 13 Sql Command ISNULL @tsqlCommand No Command Given CHAR 13 Event Type ISNULL @eventType No Event Type Given CHAR 13 Server Name ISNULL @serverName No Server Given CHAR 13 Login Name ISNULL @loginName No LOGIN Given CHAR 13 User Name ISNULL @username No User Name Given CHAR 13 DB Name ISNULL @databaseName No Database Given CHAR 13 190 7 Securing access to SQL Server Object Name ISNULL @objectName No Object Given CHAR 13 Object Type ISNULL @objectType No Type Given CHAR 13 --------------------------------------------------------- . EXEC @profile_name Admin Profile @recipients yourmail@ @subject DDL Alteration Trigger @body @emailBody GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER AuditDatabaseDDL ON ALL SERVER GO Listing DDL trigger for database creates and drops. With the trigger enabled it is easy enough to test simply by creating a database on the server create database test_trigger . As expected and as shown in Figure the mail comes in and I can see the captured events including the username that created the database as well as the time. Figure Mail from DDL trigger for database creation. 191 7 Securing access