WHEN c.name IS NULL THEN 'Default' ELSE c.name END as CategoryName , p.Name as PolicyName , CASE mandate_database_subscriptions WHEN 0 THEN 'No' ELSE 'Yes' END AS 'Mandated?' FROM syspolicy_policy_categories as c FULL OUTER JOIN syspolicy_policies as p ON c.policy_category_id = p.policy_category_id ORDER BY CategoryName We can extend this query so that we see the results of the most recent evaluation of every policy in every category: SELECT CASE WHEN c.name IS NULL THEN 'Default' ELSE c.name END as CategoryName , p.Name as PolicyName , CASE mandate_database_subscriptions WHEN 0 THEN 'No' ELSE 'Yes' END AS 'Mandated?' , target_query_expression , CASE WHEN result IS NULL THEN 'N/A' WHEN result = 1 THEN 'OK' ELSE 'Bad' END AS 'Last evaluation result' FROM syspolicy_policy_categories as c FULL OUTER JOIN syspolicy_policies as p ON c.policy_category_id = p.policy_category_id LEFT JOIN syspolicy_system_health_state AS h on p.policy_id = h.policy_id ORDER BY CategoryName The following script will show you the target for the policy that was created in the demonstration earlier in this paper: SELECT p.name, t.type_skeleton AS Target FROM dbo.syspolicy_policies AS p INNER JOIN dbo.syspolicy_object_sets as o ON p.object_set_id = o.object_set_id INNER JOIN dbo.syspolicy_target_sets as t ON o.object_set_id = t.object_set_id
ID MaximumFileSize MaximumFileSizeUnit MaximumRolloverFiles OnFailure QueueDelay ReserveDiskSpace Name int bigint int tinyint int int sysname Backup Device On Schedule Backup device BackupDeviceType PhysicalLocation SkipTapeLabel Name tinyint nvarchar(260) bit sysname Broker Priority On Schedule Broker priority ContractName ID LocalServiceName PriorityLevel RemoteServiceName Name sysname int sysname tinyint sysname sysname Broker Service On Schedule Broker service ID IsSystemObject Owner QueueName QueueSchema Name int bit sysname sysname sysname Certificate On Schedule Certificate ActiveForServiceBrokerDialog ExpirationDate ID Issuer Owner PrivateKeyEncryptionType Serial Sid StartDate Subject Thumbprint LastBackupDate Name bit datetime int nvarchar(442) sysname char(2) nvarchar(64) varbinary(85) datetime nvarchar(4000) varbinary(32) datetime sysname Credential On Schedule Credential CreateDate DateLastModified ID Identity MappedClassType ProviderName Name datetime datetime int nvarchar(4000) nvarchar(100) sysname
Provider On Schedule Cryptographic provider AsymmetricKeyExportable AsymmetricKeyImportable AsymmetricKeyPersistable AsymmetricKeySupported AuthenticationType DllPath Enabled ID ProviderGuid SymmetricKeyExportable SymmetricKeyImportable SymmetricKeyPersistable SymmetricKeySupported Name bit bit bit bit char(4) nvarchar(512) bit int bit bit bit bit sysname Data File On Schedule Data file AvailableSpace FileName Growth GrowthType ID IsPrimaryFile MaxSize Size UsedSpace IsOffline IsReadOnly IsReadOnlyMedia IsSparse Name float nvarchar(260) nvarchar(10) int bit float float float bit bit bit bit sysname Database On Schedule Database ActiveConnections AutoClose AutoShrink CompatibilityLevel CreateDate DataSpaceUsage DboLogin DefaultFileGroup DefaultSchema ID IndexSpaceUsage IsAccessible int bit bit tinyint datetime bit int int bit
IsDbAccessAdmin IsDbBackupOperator IsDbDatareader IsDbDatawriter IsDbDdlAdmin IsDbDenyDatareader IsDbDenyDatawriter IsDbOwner IsDbSecurityAdmin IsFullTextEnabled IsSystemObject LastBackupDate LastDifferentialBackupDate LastLogBackupDate Owner PrimaryFilePath ReplicationOptions Size SpaceAvailable Status UserName AnsiNullDefault AnsiNullsEnabled AnsiPaddingEnabled AnsiWarningsEnabled ArithmeticAbortEnabled AutoCreateStatisticsEnabled AutoUpdateStatisticsEnabled CaseSensitive CloseCursorsOnCommitEnabled Collation ConcatenateNullYieldsNull DatabaseOwnershipChaining IsUpdateable LocalCursorsDefault NumericRoundAbortEnabled PageVerify QuotedIdentifiersEnabled ReadOnly RecoveryModel RecursiveTriggersEnabled bit bit bit bit bit bit bit bit bit bit bit datetime datetime datetime sysname float float nvarchar(60) sysname bit bit bit bit bit bit bit bit bit sysname bit bit bit bit bit nvarchar(60) bit bit nvarchar(60) bit
AnsiWarningsEnabled ArithmeticAbortEnabled AutoClose AutoCreateStatisticsEnabled AutoShrink AutoUpdateStatisticsAsync AutoUpdateStatisticsEnabled BrokerEnabled ChangeTrackingAutoCleanUp ChangeTrackingEnabled ChangeTrackingRetentionPeriod CloseCursorsOnCommitEnabled Collation CompatibilityLevel ConcatenateNullYieldsNull CreateDate DatabaseOwnershipChaining DatabaseSnapshotBaseName DateCorrelationOptimization DefaultFileGroup DefaultFileStreamFileGroup EncryptionEnabled HonorBrokerPriority ID IsParameterizationForced IsReadCommittedSnapshotOn IsSystemObject IsUpdateable LocalCursorsDefault Name Owner NumericRoundAbortEnabled MirroringTimeout PageVerify PrimaryFilePath QuotedIdentifiersEnabled ReadOnly RecoveryModel RecursiveTriggersEnabled bit bit bit bit bit bit bit bit bit bit int bit tinyint bit datetime bit sysname bit sysname sysname bit bit int bit bit bit bit bit sysname sysname bit int nvarchar(60) bit bit nvarchar(60) bit bit
Trustworthy UserAccess nvarchar(60) Database Performance On Schedule Database AutoClose AutoShrink Size CollationMatchesModelOrMaster IsSystemObject Status bit bit float bit bit bit Database Role On Change: Prevent On Change: Log On Schedule Database role CreateDate DateLastModified ID IsFixedRole Name datetime datetime int bit sysname sysname Database Security On Schedule Database Trustworthy IsOwnerSysadmin bit bit Default On Schedule Default CreateDate ID Schema Name datetime int sysname sysname Endpoint On Schedule Endpoint EndpointState EndpointType ID IsAdminEndpoint IsSystemObject Owner ProtocolType Name nvarchar(60) nvarchar(60) int bit bit sysname nvarchar(60) sysname File Group On Schedule File ID (System.Int32) IsDefault ReadOnly Size IsFileStream Name Int bit bit float bit sysname Full Text Catalog On Schedule Full text catalog ErrorLogSize FullTextIndexSize HasFullTextIndexedTables ID ItemCount int int bit int int
PopulationCompletionDate PopulationStatus RootPath UniqueKeyCount FileGroup IsAccentSensitive IsDefault Owner Name datetime nvarchar(260) int sysname bit bit sysname sysname Full Text Index On Schedule Full text index CatalogName ChangeTracking IsEnabled PopulationStatus UniqueIndexName DocumentsProcessed ItemCount NumberOfFailures PendingChanges FilegroupName StopListName StopListOption sysname bit sysname int int int int sysname Full Text Stop List On Schedule Full text stop list ID Owner (System.String) Name int sysname On Schedule Index DisallowPageLocks DisallowRowLocks FileGroup FillFactor IgnoreDuplicateKeys IndexKeyType IsClustered IsFullTextKey IsSystemNamed IsSystemObject IsUnique NoAutomaticRecomputation PadIndex SpaceUsed IsDisabled IsPartitioned IsXmlIndex bit bit sysname tinyint bit bit bit bit bit bit bit bit bit float bit bit bit
ParentXmlIndex PartitionScheme SecondaryXmlIndexType BoundingBoxXMax BoundingBoxXMin BoundingBoxYMax BoundingBoxYMin CellsPerObject FileStreamFileGroup FileStreamPartitionScheme FilterDefinition (System.String) HasCompressedPartitions (System.Boolean) HasFilter IsSpatialIndex Level1Grid Level2Grid Level3Grid Level4Grid SpatialIndexType Name sysname sysname float float float float int sysname sysname nvarchar(max) bit bit bit sysname Linked Schedule Linked server Catalog DataAccess DataSource DistPublisher Distributor ID Location ProductName ProviderName Publisher Rpc RpcOut Subscriber CollationName ConnectTimeout LazySchemaValidation QueryTimeout UseRemoteCollation DateLastModified bit bit bit int bit bit bit bit int bit int bit datetime bit
Name (System.String) i bit Log File On Schedule Log file FileName Growth GrowthType ID MaxSize Size UsedSpace IsOffline IsReadOnly IsReadOnlyMedia IsSparse Name float int float float float bit bit bit bit sysname On Schedule Login CreateDate DateLastModified DefaultDatabase DenyWindowsLogin HasAccess IsSystemObject Language LanguageAlias LoginType Sid WindowsLoginAccessType AsymmetricKey Certificate Credential ID IsDisabled IsLocked IsPasswordExpired MustChangePassword PasswordExpirationEnabled PasswordPolicyEnforced Name datetime datetime sysname bit bit bit sysname nvarchar(60) varbinary(85) sysname sysname sysname int bit bit bit bit bit bit sysname Login Options On Change: Prevent On Change: Log On Schedule Login AsymmetricKey Certificate CreateDate Credential DefaultDatabase sysname sysname datetime sysname sysname
ID IsDisabled IsLocked IsSystemObject Language LanguageAlias MustChangePassword Name (System.String) PasswordExpirationEnabled PasswordPolicyEnforced int bit bit bit sysname bit sysname bit bit Message Type On Schedule Message type ID IsSystemObject MessageTypeValidation ValidationXmlSchemaCollection Name int bit sysname sysname sysname Multipart Name On Change Prevent On Change: Log On Schedule Function Procedure Synonym Table Type View Name Schema sysname sysname Name On Schedule Application role Asymmetric key Certificate Database role Default Index Rule Schema SQL Assembly Stored procedure Symmetric key Synonym Table Trigger User User defined function User defined type Name sysname
View XML Schema Collection Partition Function On Schedule Partition function CreateDate ID NumberOfPartitions RangeType Name datetime int int sysname Partition Scheme On Schedule Partition scheme ID NextUsedFileGroup PartitionFunction Name int sysname sysname sysname Plan Guide On Schedule Plan guide Hints ID IsDisabled Parameters ScopeBatch ScopeObjectName ScopeSchemaName ScopeType Statement Name Int Bit sysname Remote Service Binding On Schedule Remote service binding CertificateUser IsAnonymous Owner RemoteService Name sysname bit sysname sysname sysname Resource Governor On Schedule Resource governor ClassifierFunction Enabled ReconfigurePending sysname bit bit Resource Pool On Change: Prevent On Change: Log On Schedule Resource pool ID IsSystemObject MaximumCpuPercentage MaximumMemoryPercentage MinimumCpuPercentage MinimumMemoryPercentage Name int bit int int int int sysname Rule On Schedule Rule CreateDate ID DateLastModified datetime int datetime sysname
Schema Name sysname Schema On Change: Prevent On Change: Log On Schedule Schema ID Owner Name int sysname sysname Server On Schedule Server AuditLevel BackupDirectory BuildNumber DefaultFile DefaultLog ErrorLogPath InstallDataDirectory IsCaseSensitive IsFullTextInstalled Language MailProfile MasterDBLogPath MasterDBPath MaxPrecision NumberOfLogFiles OSVersion PerfMonMode PhysicalMemory Platform Processors Product RootDirectory ServiceName VersionMajor VersionMinor Collation Edition EngineEdition InstanceName IsClustered IsSingleUser NetName ProductLevel ServerType Status TapeLoadWaitTime Int Bit Bit Tinyint Int Int Int Int Int sysname sysname sysname sysname Bit Bit sysname
VersionString BrowserServiceAccount BrowserStartMode BuildClrVersionString CollationID ComparisonStyle ComputerNamePhysicalNetBIOS InstallSharedDirectory NamedPipesEnabled ResourceLastUpdateDateTime ResourceVersionString ServiceAccount ServiceInstanceId ServiceStartMode SqlCharSet SqlCharSetName SqlSortOrder SqlSortOrderName TcpEnabled FilestreamLevel FilestreamShareName SqlDomainGroup sysname int sysname bit datetime smallint smallint bit Server Audit On Schedule Server audit DefaultTraceEnabled C2AuditTracingEnabled LoginAuditLevel bit bit Server Specification On Schedule Server audit specification AuditName CreateDate DateLastModified Enabled Guid ID Name sysname datetime datetime bit int sysname Server On Change: Log On Schedule Server AdHocRemoteQueriesEnabled AffinityMask Affinity64Mask AffinityIOMask Affinity64IOMask AgentXPsEnabled AllowUpdates AweEnabled bit int int bit bit bit int bit
Text (System.String) Name sysname Server Information On Schedule Server Collation Edition ErrorLogPath IsCaseSensitive IsClustered IsFullTextInstalled IsSingleUser Language MasterDBLogPath MasterDBPath MaxPrecision NetName OSVersion PhysicalMemory Platform Processors Product ProductLevel RootDirectory VersionString EngineEdition VersionMajor VersionMinor BuildClrVersionString BuildNumber CollationID ComparisonStyle ComputerNamePhysicalNetBIOS ResourceLastUpdateDateTime ResourceVersionString SqlCharSet SqlCharSetName SqlSortOrder SqlSortOrderName (System.String) sysname sysname bit bit bit bit sysname tinyint sysname string int sysname int int sysname int int int sysname datetime sysname tinyint sysname tinyint sysname Server Installation On Schedule Server ServiceName EngineServiceAccount ServiceStartMode InstanceName ServiceInstanceIdSuffix sysname
FilestreamLevel FilestreamShareName UserInstancesEnabled Collation SqlDomainGroup LoginMode InstallDataDirectory BackupDirectory DefaultFile DefaultLog TempdbPrimaryFilePath TempdbLogPath AgentStartMode AgentServiceAccount AgentDomainGroup NamedPipesEnabled TcpEnabled InstallSharedDirectory BrowserStartMode BrowserServiceAccount tinyint sysname sysname bit bit Server Performance On Schedule Server Affinitymask Affinity64mask Affinityiomask Affinity64iomask BlockedProcessThreshold DynamicLocks Lightweightpoolingenabled MaxDegreeofParallelism CostThresholdforParallelism MaxWorkerThreads NetworkPacketSize Openobjects int int int int int int bit bit int int int int Server Security On Schedule Server LoginMode XPCmdShellEnabled bit bit bit bit bit bit
ReplaceAlertTokensEnabled ProxyAccountEnabled bit bit Server Settings On Schedule Server AuditLevel BackupDirectory DefaultFile DefaultLog LoginMode MailProfile NumberOfLogFiles PerfMonMode TapeLoadWaitTime sysname int int Service Contract On Schedule Service contract ID IsSystemObject Name Int Bit Sysname sysname Service Queue On Schedule Service queue ActivationExecutionContext CreateDate DateLastModified ExecutionContextPrincipal FileGroup ID IsActivationEnabled IsEnqueueEnabled IsRetentionEnabled IsSystemObject MaxReaders ProcedureDatabase ProcedureName ProcedureSchema RowCount Schema Name sysname datetime datetime sysname sysname int bit bit bit bit smallint sysname sysname sysname bigint sysname sysname Service Route On Schedule Service route Address BrokerInstance ExpirationDate ID datetime int
MirrorAddress RemoteService Name sysname sysname Statistic On Schedule Statistic FileGroup ID IsAutoCreated IsFromIndexCreation LastUpdated NoAutomaticRecomputation FilterDefinition HasFilter Name sysname int bit bit datetime bit varbinary(max) bit sysname Stored Procedure On Change: Prevent On Change: Log On Schedule Stored procedure AnsiNullsStatus CreateDate ForReplication ID ImplementationType IsEncrypted IsSystemObject Owner QuotedIdentifierStatus Recompile Startup AssemblyName ClassName ExecutionContext ExecutionContextPrincipal IsSchemaOwned MethodName Schema Name bit datetime bit int bit bit sysname bit bit bit sysname sysname sysname bit sysname sysname sysname Surface Area On Change: Log On Schedule Server AdHocRemoteQueriesEnabled DatabaseMailEnabled ClrIntegrationEnabled OleAutomationEnabled RemoteDacEnabled (System.Boolean) SqlMailEnabled (System.Boolean) WebAssistantEnabled XPCmdShellEnabled bit bit bit bit bit bit bit bit bit
ServiceBrokerEndpointActive SoapEndpointsEnabled bit Surface Area for AS Analysis services AdHocDataMiningQueriesEnabled UserDefinedFunctionsEnabled ListenOnlyOnLocalConnections bit bit bit bit bit Surface Area for RS Reporting services ReportManagerEnabled bit bit bit Symmetric Key On Schedule Symmetric key CreateDate DateLastModified EncryptionAlgorithm ID IsOpen KeyGuid KeyLength Owner ProviderName Name datetime datetime nvarchar(60) int bit int sysname nvarchar(120) sysname Synonym On Schedule Synonym BaseDatabase BaseObject BaseSchema BaseServer BaseType CreateDate DateLastModified ID IsSchemaOwned Schema Name sysname sysname sysname sysname sysname datetime datetime int bit sysname sysname sysname Table On Schedule Table CreateDate DataSpaceUsed FakeSystemTable FileGroup datetime bit sysname
HasClusteredIndex ID IndexSpaceUsed IsSystemObject Owner Replicated RowCount TextFileGroup AnsiNullsStatus HasAfterTrigger HasDeleteTrigger HasIndex HasInsertTrigger HasInsteadOfTrigger HasUpdateTrigger IsIndexable QuotedIdentifierStatus DateLastModified IsPartitioned IsSchemaOwned PartitionScheme ChangeTrackingEnabled FileStreamFileGroup FileStreamPartitionScheme HasCompressedPartitions LockEscalation TrackColumnsUpdatedEnabled Schema Name bit int float bit sysname bit bigint sysname bit bit bit bit bit bit bit bit bit datetime bit bit bit sysname bit sysname sysname bit bit sysname sysname Table Options On Change: Prevent On Change: Log On Schedule Table AnsiNullsStatus ChangeTrackingEnabled CreateDate FakeSystemTable ID IsSchemaOwned IsSystemObject LockEscalation Name Owner bit bit datetime bit int bit bit bit sysname sysname
QuotedIdentifierStatus Replicated Schema TrackColumnsUpdatedEnabled bit bit sysname bit Trigger On Schedule Trigger AnsiNullsStatus CreateDate Delete DeleteOrder ID ImplementationType Insert InsertOrder InsteadOf (System.Boolean) IsEnabled IsEncrypted (System.Boolean) IsSystemObject NotForReplication QuotedIdentifierStatus Update UpdateOrder AssemblyName ClassName DateLastModified ExecutionContext ExecutionContextPrincipal MethodName Name bit bit bit int bit bit bit bit bit bit bit bit sysname sysname datetime sysname sysname sysname User On Schedule User CreateDate DateLastModified HasDBAccess ID IsSystemObject Login LoginType Sid UserType AsymmetricKey Certificate DefaultSchema Name datetime datetime bit int bit sysname varbinary(85) sysname sysname sysname sysname
Login LoginType Name Sid UserType bit sysname varbinary(85) View On Schedule View AnsiNullsStatus CreateDate HasColumnSpecification ID IsEncrypted IsSchemaBound IsSystemObject Owner QuotedIdentifierStatus HasAfterTrigger HasDeleteTrigger HasIndex HasInsertTrigger HasInsteadOfTrigger HasUpdateTrigger IsIndexable DateLastModified IsSchemaOwned ReturnsViewMetadata Schema Name bit datetime bit int bit bit bit sysname bit bit bit bit bit bit bit bit datetime bit bit sysname sysname View Change: Prevent On Change: Log On Schedule View AnsiNullsStatus CreateDate ID IsEncrypted IsSchemaBound IsSchemaOwned IsSystemObject Name Owner Schema QuotedIdentifierStatus ReturnsViewMetadata bit datetime int bit bit bit bit sysname sysname sysname bit bit Workload Group On Change: Prevent On Change: Log On Schedule Workload group GroupMaximumRequests ID Importance int int