Creating a policy to lockdown table changes

• SQLSandwiches

  SSCrazy

  Points: 2143

  More actions

  September 15, 2009 at 12:55 pm

  #204682

  Hello,

  I am looking for a way to lock down all table changes past a certain date. I want to use SQL Policy Management and create policy that will prevent anyone from making any changes to any table in a certain database past a certain date.

  I have thought about creating a condition using @CreateDate >= (whatever date) and @DateLastModifed >= (whatever date). The problem with this is that these facets are not supported with the On Change modes.

  Any ideas?

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  September 15, 2009 at 1:34 pm

  #1054078

  You could use a DDL trigger.. This would be pretty much the heart of it:

  DECLARE @EventData xml

  SET @EventData = EVENTDATA()

  --SELECT @EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),

  -- @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),

  -- @EventData.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)'),

  -- @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'),

  -- @EventData.value('(/EVENT_INSTANCE/SPID)[1]', 'int'),

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

  -- @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)'),

  -- @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)'),

  -- @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),

  -- @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)')

  IF ( @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)') = 'YourDatabaseName'

  AND @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)') IN ( 'CREATE_TABLE', 'ALTER_TABLE', 'DROP_TABLE' )

  AND GETDATE() > '09/16/2009' )

  BEGIN

  ROLLBACK

  END

  This code is a hack from a loggin solution I have been working on for a while..

  CEWII