Creating a policy to lockdown table changes

  • 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?

  • 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' )




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


Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply