Post reply

How to create Rule or Trigger for Object Names

  • March 13, 2012 at 3:16 pm

    #252446

    HI there!

    I'd like to know if there is an existing rule or trigger I can use for developers not to create objects with dashes or hyphes. For example, tables, databases and linked servers?

    Any help greatly appreciated!

    I know we have policies in 2008 but we're not there yet! 🙁

    ¤ §unshine ¤

  • March 13, 2012 at 3:40 pm

    #1458891

    you could do it with a suite of DDL triggers, but would it not be easier to tell the developers not to do it.

    it seems a little extreme to do a DDL trigger for something that basic.

    I think you'd need a database trigger FOR DDL_DATABASE_LEVEL_EVENTS for each database to be "protected", , an all server trigger for CREATE_DATABASE and ALTER_DATABASE, and i'm not sure which event is for linked servers, but it would be for ALL SERVER as well.

    edit:

    the event is CREATE_LINKED_SERVER, and it is a a server scoped event.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 13, 2012 at 4:43 pm

    #1458920

    Do you have a SQL 2008 instance anywhere in your environment? Even a shared dev or private local instance? If so, or if you can stand one up, you can store policies on it and then evaluate them against SQL 2005 (or 2000) instances to draw a report of failed policies on the remote instance. It will not prevent a person from violating the policy initially, but if you run your evaluations nightly against all your instances you can know within 24 hours that something is misaligned and address it before it moves too far through the development cycle.

    I am in the process of implementing something like this in a mixed environment containing 2005 and 2008R2.

    Here is the article that got me started:

    SQL Server Policy-Based Management > Running Against SQL Server 2005 and SQL Server 2000

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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