{"id":130,"date":"2015-09-08T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/policy-based-management-tsql2sday-70"},"modified":"2026-03-18T21:52:08","modified_gmt":"2026-03-18T21:52:08","slug":"policy-based-management-tsql2sday-70","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2015\/09\/08\/policy-based-management-tsql2sday-70\/","title":{"rendered":"Policy Based Management Templates – TSQL2sDay #70 – SQL Hammer"},"content":{"rendered":"

T-SQL Tuesday (aka #TSQL2sday) is a monthly SQL Server blogger event started back in late 2009 by Adam Machanic (blog<\/a> | twitter<\/a>). The first topic was about Date\/Time Tricks. Read\u00a0the original TSQL2sday call-out<\/a>. Each month a different SQL Server blogger is the host who announces the topics and performs the round-up. A big thanks goes out to Jen McCown (b<\/a> | t<\/a>), one of the renowned\u00a0Midnight DBAs<\/a>, for hosting T-SQL Tuesday #70, strategies for managing an enterprise<\/a>.<\/p>\n

\"\"<\/a><\/p>\n

As one of my strategies for managing an enterprise I\u2019d like to talk about Policy Based Management<\/a>. Policy based management (PBM) enables you to verify best practices and corporate policies across a large enterprise. You execute policy checks from a central management server and retrieve results for those that violate the policies. PBM is a great way of finding loose threads and keeping your enterprise tight.<\/p>\n

For someone just starting out with policy based management, it can be a challenge to brainstorm every best practice or policy that you would like to enforce. That is the reason for this blog post. Microsoft has provided us almost six\u00a0dozen policies with the installation of SQL Server which can be imported as a great jump-off point with PBM. The unfortunate part is that you would not be able to find these best practice policies by just stumbling around in SQL Server Management Studio.<\/p>\n

Importing policy based management templates<\/h2>\n

MSDN informs of us the location of the PBM templates here<\/a>. The exact file path will vary for each version.<\/p>\n

> C:\\Program Files\\Microsoft SQL Server\\130\\Tools\\Policies\\DatabaseEngine\\1033<\/p>\n

Begin by opening SQL Server Management Studio (SSMS) and navigating to Management > Policy Management.<\/p>\n

\"\"<\/a><\/p>\n

Right-click Policies and select Import Policy\u2026<\/p>\n

\"\"<\/a><\/p>\n

Browse to the path of the policy files, select all of the files, and click open.<\/p>\n

\"\"<\/a><\/p>\n

On the next dialog box, decide whether you want these policies to be imported in the disabled or enabled state and select OK.<\/p>\n

\"\"<\/a><\/p>\n

That\u2019s it!\u00a0Your policies have been imported.\u00a0<\/strong><\/p>\n

We now have 66 imported policies and 89 new conditions.<\/p>\n

SELECT COUNT() [PolicyCount] FROM msdb.dbo.syspolicy_policies\nSELECT COUNT(<\/em>) [ConditionCount] FROM msdb.dbo.syspolicy_conditions<\/code><\/pre>\n
\"\"<\/a><\/p>\n
These are simply a starting point. I am not recommending that you start modifying your servers to meet these conditions. What I am recommending is that you evaluate each policy and decide whether or not it fits your enterprise. Drill into the Policies folder in SSMS\u2019s object explorer and view the policy properties for a description. Note that there is an\u00a0address<\/em> block where you can reference more information on MSDN. You can also look at the full list of policies and their details here<\/a>.<\/p>\n
\"\"<\/a><\/p>\n
When you decide to implement one of the policies, edit it and add a schedule to catch drift from your policies.<\/p>\n
\"\"<\/a><\/p>\n
From here you should be well on your way towards establishing your enterprise\u2019s policies and have an efficient method of enforcing them.<\/p>\n
\n
\"\"Written by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\n
Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n
Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n
For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
T-SQL Tuesday (aka #TSQL2sday) is a monthly SQL Server blogger event started back in late 2009 by Adam Machanic (blog | twitter). The first topic was about Date\/Time Tricks. Read\u00a0the original TSQL2sday call-out. Each month a different SQL Server blogger is the host who announces the topics and performs the round-up. A big thanks goes […]<\/p>\n","protected":false},"author":1,"featured_media":541,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-130","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/130","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=130"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/130\/revisions"}],"predecessor-version":[{"id":392,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/130\/revisions\/392"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/541"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=130"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=130"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}