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
