asked
SQL Policy to enforce object naming standards across User Databases
Apologies in advance if this is a stupid question as I am new to SQL Server Policy Management but here goes: - I am trying to set up a policy that will enforce object naming standards across all user databases however I can’t assign my condition against targets during the policy creation stage! I have followed several posts to get me started and had no issues setting a policy up against an individual database however when I try to amend my database condition I receive an error! 1) Condition UserDatabases a. Field - @Name b. Operator – Not In c. Value – Array(‘master’, ‘msdb’, ‘model’) 2) Condition StoredProcNamingStandard a. Field - @Name b. Operator – Like c. Value – ‘usp_%’ 3) Policy a. Check Condition – StoredProcNamingStandard i. Against Targets – 1. Every StoredProcedure 2. UserDatabase – Database (this is my condition I want to use for user db’s only) Now this is where I have an issue my condition is not available in the drop down for the target Database and even if I try to create a new one from here it does not allow me to assign it, obviously I am doing something wrong so any help/guidance would be greatly appreciated. Thankyou.
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
Here is an article written by Aaron Bertrand which should be able to answer all of your questions. You can test different scenarios like excluding system databases or others.
https://www.mssqltips.com/sqlservertip/2298/enforce-sql-server-database-naming-conventions-using-policy-based-management/ and another by Ryan Adams:
http://www.ryanjadams.com/2011/02/sql-naming-conventions-pbm-sps/#axzz45d19YGbb
I would like to refer you the Microsoft's sample databases please check this
http://sqlserversamples.codeplex.com/. Hopefully it will be helpful to you.
20 People are following this question.
