SQL Server Policy Based Management (PBM)
Introduction
"A policy is typically described as a principle or rule to guide decisions and achieve rational outcomes. The term is not normally used to denote what is actually done, this is normally referred to as either procedure or protocol. Policies are generally adopted by the Board of or senior governance body within an organization whereas procedures or protocols would be developed and adopted by senior executive officers" - Wikipedia
Policy-based Management was introduced in SQL Server 2008 and is a useful tool for SQL Server Administrators.
Case Study
As a database administrator we are required by our company to enforce the following Task:
1. All "user stored procedure:" must be named with a prefix of 'usp_'
2. All "user table" must be named with a prefix of 'tbl_'
We are to implement this policy across all SQL server in the company
Step-By-step Guide for building SQL Server PBM
To implement the case requirements above. We must do the following step-by-step.
- Open SQL Server Management Studio
- Go to Management > policy management > right-click > New policy
3. Enter a new policy name and type "Stored proc must be prefix USP_".
*Figure 1. Creating a new policy
*4. Click Condition and create a new condition as shown.
Figure 2. Creating a new Condition.
- Enable and Configure the policy for "OnChange:prevent" evaluation mode. This will prevent the user from creating a stored procedure that violates the policy.
Figure 3. On change prevent Evaluation Mode
6. On server restriction build a new condition to target the local server as shown
Figure 4. Create a target server condition