Post reply

Policy Management for new tables only?

  • November 9, 2008 at 1:49 am

    #195679

    Hi,

    I'm testing out the Policy Management feature. I'm trying to implement a simple policy that will prevent new tables that are not prefixed with 'tbl'. My question is...

    How can I make a policy that will check only new tables? I've tried creating this policy through the GUI in SSMS and TSQL, but get error stating that can't filter a table by the creation date.

    My goal is to have only new tables comply to this new policy. Setting up this policy in a brand new database isn't a problem because I don't have 'old' tables to worry about not meeting the policy. Problem is with existing tables in an existing databse; I want to use the policy because it's a great clean way to ensure table naming convention is followed.

    I've created a "TableNameCondition" that uses the "Multipart" facet, it checks the field "@Name" and checks if begins with tbl (LIKE 'tbl%' is the condition). I've created a second condition called "DevelopmentDatabasesCondition" that uses the "Database" facet, it checks the field "@Name" to match my development databse (= 'DevDB' is the condition). I've created a third condition called "NewTablesCondition" that uses the "Tables" facet, it checks the "@CreateDate" field to be later than 10/31/2008.

    I create a new policy and set "CheckCondition" to: TableNameCondition. I then set the "EVERY Table in EVERY Database". I can easily change the "EVERY Database" to "DevelopmentDatabasesCondition Database". The policy is Enabled, and the Evaluation Mode is set to "On change: prevent".

    I can't select the "NewTablesCondition" for the EVERY in "EVERY Table". I already tried selecting the "New Condition..." option under the EVERY Table target, I create the condition as I did with NewTablesCondition and everything creats fine. When I go to change the EVERY the condition isn't visible.

    This somehow is not allowed to be a condition to be used against table targets, not sure why.

    If I bypass the GUI in SSMS and write the TSQL to use the NewTablesCondition for the Database/Table node I can submit it just fine; but when I create a table the Policy isn't triggered. If I try to create a table called 'test' then it still is created. I re-open the "NewTablesCondition" and see the following error:

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Condition 'NewTablesCondition' cannot be used for filtering.

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

    Thanks for any help you can provide,

    James

    ~ Without obstacles, you cannot progress ~
    http://sqln.blogspot.com/

  • September 15, 2009 at 11:31 am

    #1053990

    I had the same problem. Check it out here

    Worked perfectly for me.

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

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