Manually Enforcing Policies in SQL Server 2008
The Declarative Management Framework allows you to manually reconfigure policy targets so that they comply with some policies. It is important to note, however, that this manual reconfiguration cannot resolve all policy compliance issues. SQL Server will be able to modify database settings, reconfigure permissions and perform similar tasks but it cannot, for example, change the name of a stored procedure without administrator intervention.
Difficulty: Easy
Time Required: 15 minutes
Here's How:
- Open Microsoft SQL Server Management Studio (SSMS).
- Expand the Management folder.
- Expand the Policies folder.
- Scroll through the list of policies until you find the policy you wish to enforce. Right-click on it and choose Test Policy from the pop-up menu.
- The results window will soon appear, showing you the current compliance status of the targets against the selected policy. Review these results and identify the non-compliant target(s) you wish to reconfigure.
- Click on the first target you wish to remediate and then click the Configure button. SQL Server will then attempt to reconfigure the target into a compliant status.
- Repeat this process for other targets you wish to remediate.
Tips:
- Not all conditions may be remediated using this procedure. Review the reconfiguration results carefully to identify non-compliance issues that require administrator intervention.
- Remember that this manual remediation process is a one-time change. It will resolve policy compliance issues, but will not prevent the target from falling out of compliance at a later date. If you elect to use manual policy enforcement, you need to repeat this process on a periodic basis to ensure your databases remain compliant with your policy.
What You Need:
- Microsoft SQL Server 2008
- SQL Server Management Studio
Source...