Skip to content

SQL Server Policy Based Management (PBM)

    Policy Based Management is a system that allows the DBA to manage and control on a granular scale how to best apply rules and standards to the entire server of specific objects

    Benefits of Policy-Based Management

    • Allows the DBA to develop and enforce common, enter­prise-wide standards for the SQL Server
    • Allows the DBA to prevent, enable, and disable certain conditions of objects
    • Allows the DBA to propagate a set of rules and conditions to the whole server or objects and find inconsistencies
    • Allows the DBA to automatically check each instance for compliance on a regular basis rather than manually

    Policy-Based Management Terms

    Facets:

    A facet is a collection of pre-defined properties of an object that describe some functionality of SQL Server. For example, the Database (Auto Close, Auto Shrink)

    Conditions.

    Once you have selected a facet that you want to create a policy for, you must specify a condition for that facet.  For example, if you want the Auto Shrink property of the Database Options facet set to “false,” you must create a condition for this facet

    PROCEDURE:

    • FIND THE FACET YOU WANT TO APPLY THE CONDITION ON
    • SET THE CONDITION
    • APPLY THE POLICY
    --EXAMPLE 1 : CREATE 10 DATABASE, SOME WITH AUTOSHRINK PROPERTY OF THE FACET (DATABASE) SET TO 'ON'
    
    --FIND ALL THE DATABASE WITH THE AUTOSHRINK PROPERTY SET TO 'ON' AND SET THEM TO 'OFF'
    
    --SOLUTIONS:  CREATE A POLICY BASED CONDITION
    
    USE [master]
    GO
    
    CREATE DATABASE T1
    GO
    
    ALTER DATABASE [T1] 
    SET AUTO_SHRINK ON 
    WITH NO_WAIT
    GO
    
    CREATE DATABASE T2
    GO
    
    CREATE DATABASE T3
    GO
    
    CREATE DATABASE T4
    GO
    
    ALTER DATABASE [T4] 
    SET AUTO_SHRINK ON 
    WITH NO_WAIT
    GO
    
    CREATE DATABASE T5
    GO
    
    
    CREATE DATABASE T6
    GO
    
    ALTER DATABASE [T6] 
    SET AUTO_SHRINK ON 
    WITH NO_WAIT
    GO
    
    CREATE DATABASE T7
    GO
    
    CREATE DATABASE T8
    GO
    
    CREATE DATABASE T9
    GO
    
    CREATE DATABASE T10
    GO
    
    
    --USE [master]
    --GO
    --ALTER DATABASE [T1] 
    --SET AUTO_SHRINK OFF 
    --WITH NO_WAIT
    --GO
    
    --DROP PROCEDURE POLICY_BASED
    --------------------------------------------
    
    --EXAMPLE 2:  PREVENT A USER FROM CREATING A STORED PROCEDURE THAT DOES NOT USE A 'USP_' PREFIX
    
    --FACET = MULTIPART NAME
    
    USE SQL2
    GO
    
    CREATE PROCEDURE POLICY_BASED  --<< NOTE THAT THE PREFIX 'USP_' HAS NOT BEEN USED, AND AS SUCH SPROC WILL NOT BE CREATED 
    AS
    SELECT * FROM [dbo].[People2]
    
    --AS SOON AS THE SPROC HAS THE PREFIX 'USP_', IT'S CREATED
    
    USE SQL2
    GO
    
    CREATE PROCEDURE USP_POLICY_BASED  --<< NOTE THAT THE PREFIX 'USP_' HAS NOT BEEN USED, AND AS SUCH SPROC WILL NOT BE CREATED 
    AS
    SELECT * FROM [dbo].[People2]
    
    -- Find all databases with AutoShrink turned on. 
    
    
    declare @DatabaseName sysname
    
    if left(convert(nvarchar, serverproperty('productversion')), 1) <> '8'
    begin
        declare cur cursor local fast_forward for
            select name   as 'Database Name'
              from sys.databases
             where is_auto_shrink_on = 1
               and state_desc = 'ONLINE'
               and database_id > 4
    
        open cur
        fetch next from cur into @DatabaseName
    
        while @@fetch_status = 0
        begin
            print @DatabaseName
            fetch next from cur into @DatabaseName
        end
    
        close cur
        deallocate cur
    end
    else
    begin
        declare cur cursor local fast_forward for
            select name   as 'Database Name'
              from master..sysdatabases
             where status & 4194304 = 4194304
               and status & 512 = 0
               and dbid > 4
    
        open cur
        fetch next from cur into @DatabaseName
    
        while @@fetch_status = 0
        begin
            print @DatabaseName
            fetch next from cur into @DatabaseName
        end
    
        close cur
        deallocate cur
    end

    A very common pattern to begin is the creation of conditions to avoid the policy check against system databases and system tables. Let’s do a step-by-step walkthrough to achieve this.

    Avoiding System Databases

    We need two conditions for this: One to check system databases and another to check system tables. Let’s start:

    1. Using SSMS, right-click the ‘Conditions’ folder, below ‘management’->’Policy Management’ and click the ‘new condition’ menu item to open the ‘Create new condition’ window.
    2. In the ‘Create new condition’ window, type ‘Not System Database’ in the ‘Name’ textbox. It will be the name of our first condition
    3. In the ‘Create new condition’ window, in the ‘Facet’ drop-down list, select ‘Database’, so we can create a condition against database properties.Each ‘Facet’ has several fields. Each field represents information that we can check in the object. There is a field in the ‘Database’ facet named ‘@IsSytemObject’ that we can use to identify whether the database is a system database. In our case, we need the condition to be true if the database is NOT a system database, so the policy check will continue
    4. In the ‘Create new condition’ window, in the ‘Field’ column inside the ‘Expression’ grid, select ‘@IsSystemObject’
    5. Select ‘=’ in the ‘Operator’ column inside the ‘Expression’ grid
    6. Choose ‘False’ in the ‘Value’ column inside the ‘Expression’ grid
    7. In the ‘Create new condition’ window, click the‘Ok’ button

    Creating the ‘Auto Shrink’ condition

    The main condition of our first policy will need to check whether the ‘Auto Shrink’ database option is enabled. Let’s build it:

    1. Using SSMS, right-click the ‘Conditions’ folder, below ‘management’->’Policy Management’ and click the ‘new condition’ menu item to open the ‘Create new condition’ window.
    2. In the ‘Create new condition’ window, type ‘AutoShrink’ in the ‘Name’ textbox: It will be the name of our first condition
    3. In the ‘Create new condition’ window, in the ‘Facet’ drop-down list, select ‘Database’, so we can create a condition against database properties.
    4. In the ‘Create new condition’ window, in the ‘Field’ column inside the ‘Expression’ grid, select ‘@AutoShrink’
    5. Select ‘=’ in the ‘Operator’ column inside the ‘Expression’ grid
    6. Choose ‘False’ in the ‘Value’ column inside the ‘Expression’ grid. The Auto Shrink option should be disabled, so our policy will pass if ‘@AutoShrink’ is false.
    7. In the ‘Create new condition’ window, click ‘Ok’ buttonThe two conditions that we’ve created so far will appear in SSMS like the image below:

    Creating the first policy: Check for ‘Auto Shrink’ option

    Let’s use our condition in a new policy.

    1. Right-click the ‘Policies’ folder, below ‘management’->’Policy Management’ and click the ‘new policy’ menu item to open the ‘Create new policy’ window.
    2. In the ‘Create new policy’ window, type ‘AutoShrink’ in the ‘Name’ textbox, it will be the name of our first policy
    3. In ‘Check condition’ drop-down list, select ‘AutoShrink’, the main condition of our policy
    4. In the ‘Against Target’ box, click the arrow besides ‘Every Database’ and select ‘Not System Database’, so our policy won’t be checked for system databases.
    5. Click ‘Ok’ button
    6. Right-click the ‘AutoShrink’ policy and click the ‘Evaluate’ context menu item
    7. Check the result, you will be able to see if any database has the ‘Auto Shrink’ option enabled, then click ‘Ok’ button.

    Avoiding policy checks on system tables

    We need just one more condition that will filter the tables for which our policies will be checked. We don’t want our policies checking system tables.

    Let’s name our new condition as ‘Not System table’. The condition will use the ‘Table’ facet, because it will be executed to check each table. The ‘Table’ facet also has the ‘@IsSystemObject’ field, so we can use it in the same way we did with the previous condition.

    1. Right-click the ‘Conditions’ folder, below ‘management’->’Policy Management’ and click ‘new condition’ menu item to open ‘Create new condition’ window.
    2. In the ‘Create new condition’ window, type ‘Not System Table’ in the ‘Name’ textbox, it will be the name of our first condition
    3. In the ‘Create new condition’ window, in the ‘Facet’ drop-down list, select ‘Table’, so we can create a condition against database properties.
    4. In the ‘Create new condition’ window, in the ‘Field’ column inside the ‘Expression’ grid, select ‘@IsSystemObject’
    5. Select ‘=’ in the ‘Operator’ column inside the ‘Expression’ grid
    6. Choose ‘False’ in the ‘Value’ column inside the ‘Expression’ grid
    7. In the ‘Create new condition’ window, click ‘Ok’ button