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

1 comment:

Radley Co Tad said...

This is an amazing blog,it gives very helpful messages to us.Besides that Wisen has established as Best Dot Net Training in Chennai. or learn thru ASP.NET Online Training . Nowadays Dot Net has tons of job opportunities on various vertical industry.