/*
IF EXISTS (SELECT * FROM master.sys.server_triggers
WHERE parent_class_desc = 'SERVER' AND name = N'ddl_SQL_Server_Schema_Lock')
DROP TRIGGER [ddl_SQL_Server_Schema_Lock] ON ALL SERVER;
*/
CREATE TRIGGER [ddl_SQL_Server_Schema_Lock]
ON ALL SERVER
FOR ALTER_PROCEDURE, DROP_PROCEDURE,
ALTER_TABLE, DROP_TABLE,
ALTER_FUNCTION, DROP_FUNCTION,
ALTER_INDEX, DROP_INDEX,
ALTER_VIEW, DROP_VIEW,
ALTER_TRIGGER, DROP_TRIGGER
AS
BEGIN
/*Record the change and who made it, we must do this before our ROLLBACK otherwise we lose access to the EventData*/
DECLARE @EventData XML,
@DatabaseName varchar(800),
@Schema SYSNAME,
@Object SYSNAME,
@EventType SYSNAME,
@SQL VARCHAR(max)
SET @EventData = EventData()
SET @DatabaseName = '' + @EventData.value('data(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(50)')
SET @Schema = @EventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'VARCHAR(50)')
SET @Object = @EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(50)')
SET @EventType = @EventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(50)')
SET @SQL = @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(max)')
/*Check user before allowing changes*/
DECLARE @Msg varchar(MAX),@Blocked bit=0;
IF (SELECT count(*) WHERE SYSTEM_USER IN ('NT AUTHORITY\SYSTEM','Domain\AllowUserName')) = 0
BEGIN
SET @Msg = 'Direct changes to LIVE are prohibited, Changes made to LIVE need to go through the roll out process';
SET @Blocked = 1
ROLLBACK;
RAISERROR (@Msg,16,1);
--print @Msg;
--print'blocked'
END
--Continue to log this event to our logging table
INSERT INTO [SFCLWareHouse].dbo.SQL_Server_SchemaAudit
(AuditDate, UserName, DataBaseName, [Event], [Schema], Object, TSQL, [XMLEventData],Blocked)
SELECT
GetDate(),
SYSTEM_USER,--@EventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME')
@DatabaseName,
@EventType, @Schema, @Object,
@SQL,
@EventData,
@Blocked
/*Notify of blocked changes if the event was not allowed*/
IF @Blocked = 1
BEGIN
DECLARE @MailID int, @HTMLBody varchar(max)
SET @HTMLBody ='An attempt has been made at changing the SQL Schema for sqlprod.swisscantouk.local <br><br>' +
'<TABLE style="border:none"><TD>Time:</TD><TD>' + CAST(GETDATE() as varchar(MAX)) + '</TD></TR>' +
'<TD>User:</TD><TD>' + SYSTEM_USER + '</TD></TR>' +
'<TD>Database:</TD><TD>' + @DatabaseName + '</TD></TR>' +
'<TD>Event:</TD><TD>' + @EventType + '</TD></TR>' +
'<TD>ObjectName:</TD><TD>' + @Object + '</TD></TR>' +
'<TD>TSQL:</TD><TD>' + @SQL + '</TD></TR>'
EXECUTE [DataWareHouse].[dbo].[Mail_INSERT] @MailID = @MailID OUTPUT
,@To = 'monitoredmailbox@domain.com'
,@Subject = 'SQL Prod Schema change attmept'
,@BodyHTML = @HTMLBody
,@Importance = 1
END
END