SQL Server 2016 Hosting – HostForLIFE.eu :: SQL Server DDL Triggers

Peter Cole | March 7, 2018 | Other Related Posts | No Comments

In this post, I will write an article about SQL Server DDL Triggers. DDL triggers are an exceptional sort of trigger that fire in light of Data Definition Language (DDL) proclamations. They can be utilized to perform regulatory assignments in the database, for example, examining and managing database operations. This segment gives the data that is obliged to comprehend, plan, and execute DDL triggers.

DDL triggers are an exceptional sort of trigger that fire in response to Data Definition Language (DDL) statements. They can be utilized to perform regulatory assignments in the database, for example, examining and managing database operations. This segment gives the data that is obliged to comprehend, plan, and execute DDL triggers.

And here is the script that I used:

CREATE TRIGGER trgLogDDLEvent ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
    DECLARE @data XML
    SET @data = EVENTDATA()
    select @data
    IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
        <> 'CREATE_STATISTICS' 
        INSERT  INTO dba.dbo.DDLChangeLog ( DatabaseName, InsertionDate, CurrentUser ,LoginName ,Username,
                            EventType, ObjectName, ObjectType, tsql)
        SELECT   
                @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
                @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'Datetime'),
                @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)'),
                @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
                @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)'),
                @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
                @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)'),
                @data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)'),
                @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')

GO

ALTER TRIGGER Dont_use_sa_please ON DATABASE
FOR CREATE_EXTENDED_PROPERTY
,CREATE_USER ,DROP_FUNCTION,DROP_VIEW,ALTER_PROCEDURE,ADD_ROLE_MEMBER
,CREATE_INDEX,ALTER_TABLE,CREATE_TABLE,ALTER_FUNCTION,CREATE_PROCEDURE
,ALTER_PARTITION_SCHEME,ALTER_VIEW,ALTER_AUTHORIZATION_DATABASE
,ALTER_TRIGGER,CREATE_FUNCTION,DROP_INDEX,DROP_PROCEDURE,ALTER_INDEX
,DROP_TABLE,RENAME,GRANT_DATABASE,ALTER_PARTITION_FUNCTION

AS
    DECLARE @data XML
    SET @data = EVENTDATA()
--SELECT  @data
    DECLARE @Message VARCHAR(255)
    IF @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') = 'sa'
    BEGIN
        SELECT  @message = 'You are forbidden to '+REPLACE( @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), '_', ' ') +'  '
                + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '''; Please use Windows Authentication to connect to the server '
        RAISERROR ( @Message, 16, 1 )
        ROLLBACK
    END
GO

CREATE TABLE testTable( i INT )
DROP TABLE testTable

CREATE TRIGGER trgLogDDLEvent ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

DECLARE @data XML

SET @data = EVENTDATA()

select @data

IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

<> 'CREATE_STATISTICS'

INSERT INTO dba.dbo.DDLChangeLog ( DatabaseName, InsertionDate, CurrentUser ,LoginName ,Username,

EventType, ObjectName, ObjectType, tsql)

SELECT

@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/PostTime)[1]', 'Datetime'),

@data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')

ALTER TRIGGER Dont_use_sa_please ON DATABASE

FOR CREATE_EXTENDED_PROPERTY

,CREATE_USER ,DROP_FUNCTION,DROP_VIEW,ALTER_PROCEDURE,ADD_ROLE_MEMBER

,CREATE_INDEX,ALTER_TABLE,CREATE_TABLE,ALTER_FUNCTION,CREATE_PROCEDURE

,ALTER_PARTITION_SCHEME,ALTER_VIEW,ALTER_AUTHORIZATION_DATABASE

,ALTER_TRIGGER,CREATE_FUNCTION,DROP_INDEX,DROP_PROCEDURE,ALTER_INDEX

,DROP_TABLE,RENAME,GRANT_DATABASE,ALTER_PARTITION_FUNCTION

DECLARE @data XML

SET @data = EVENTDATA()

--SELECT @data

DECLARE @Message VARCHAR(255)

IF @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') = 'sa'

BEGIN

SELECT @message = 'You are forbidden to '+REPLACE( @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), '_', ' ') +' '

+ EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '''; Please use Windows Authentication to connect to the server '

RAISERROR ( @Message, 16, 1 )

ROLLBACK

END

CREATE TABLE testTable( i INT )

DROP TABLE testTable

HostForLIFE.eu SQL Server 2014 Hosting

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. They deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. They have customers from around the globe, spread across every continent. They serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

HostForLIFE.eu SQL Server 2014 Hosting

Related Posts

About The Author

Pete