Search This Blog

DDL triggers

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

No comments: