MSSQL – Transfer logins and passwords between servers

Posted on March 3, 2026 by author1

Transfer logins and passwords to destination server (Server A) using scripts generated on source server (Server B)

On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.
Open a new Query Editor window, and then run the following script.

USE master
GO
IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint – (@firstint*16)
SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID (‘sp_help_revlogin’) IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’
ELSE
DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT ‘No login(s) found.’
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = ‘/* sp_help_revlogin script ‘
PRINT @tmpstr
SET @tmpstr = ‘** Generated ‘ + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
PRINT @tmpstr
PRINT ”
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
    PRINT ”
    SET @tmpstr = ‘– Login: ‘ + @name
    PRINT @tmpstr
    IF (@type IN ( ‘G’, ‘U’))
    BEGIN — NT authenticated account/group

      SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
    END
    ELSE BEGIN — SQL Server authentication
        — obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        — obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string + ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN — login is denied access
      SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN — login exists but does not have access
      SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN — login is disabled
      SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
    END
    PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Note This script creates two stored procedures in the master database. The procedures are named sp_hexadecimal and sp_help_revlogin.

Run the following statement in the same or a new query window:

EXEC sp_help_revlogin

The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

Steps on the destination server (Server B):

On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

Important Before you go to step 2, review the information in the “Remarks” section below.
Open a new Query Editor window, and then run the output script that’s generated in step 2 of the preceding procedure.

For more information refer to this webpage

https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

DR_encryption_solution_for_failing_over_encrypted_table-column

Posted on March 3, 2026 by author1

USE [CreditDataWarehouse]
GO

/*
**
** Desc: Adds/Resets Master key password to prod database
**
** Auth : Olafsson
**
** Date: 6-May-2019

** Change History

** PR Date Author Description
** — ——– ——- ————————————
**
*/

IF OBJECT_ID(‘dbo.C0012433944’, ‘U’) IS NOT NULL
BEGIN
DROP TABLE dbo.C0012433944
PRINT ‘<<< Success : Dropped dbo.C0012433944 >>>’
END
GO

DECLARE @PASSWORD VARCHAR(128) = CONVERT(VARCHAR(128),CRYPT_GEN_RANDOM(128),1)

SELECT @PASSWORD AS secret, @@SERVERNAME AS servername INTO dbo.C0012433944

DECLARE @SQL VARCHAR(255) = ‘ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = ”’ + @PASSWORD + ””

Exec(@SQL)

PRINT ‘EXECUTED ALTER MASTER KEY ENCRYPTION’

— Revoke all permissions on table C0012433944 to everyone except sysadmins

use [CreditDataWarehouse]
go

DENY delete ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY insert ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY references ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY select ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY update ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO

DENY delete ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY insert ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY references ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY select ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY update ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO

DENY delete ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY insert ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY references ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY select ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY update ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO

DENY delete ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY insert ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY references ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY select ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY update ON OBJECT::dbo.C0012433944 TO CMPROD;
GO

DENY delete ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY insert ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY references ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY select ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY update ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO

DENY delete ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY insert ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY references ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY select ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY update ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO

DENY delete ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
DENY insert ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
DENY references ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
DENY select ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
DENY update ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO

— Create Switcher job on both SQL Instances
USE [msdb]
GO

/ Object: Job [CreditDataWarehouse DB Encryption switcher] Script Date: 06/05/2019 10:11:32 / BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 / Object: JobCategory [[Uncategorized (Local)]] Script Date: 06/05/2019 10:11:32 /
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’CreditDataWarehouse DB Encryption switcher’,
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’A job to monitor the CreditDataWarehouse database for failover and if one is spotted to update the master key to re-enable decryption.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/ Object: Step [Check for failover and if so refresh master key] Script Date: 06/05/2019 10:11:32 /
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Check for failover and if so refresh master key’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’IF EXISTS(select * from sys.databases where name=”CreditDataWarehouse” and state_desc=”ONLINE”)
BEGIN
IF EXISTS(SELECT * FROM CreditDataWarehouse.dbo.C0012433944 WHERE [servername] != @@SERVERNAME)
BEGIN
DECLARE @PASSWORD VARCHAR(128)
SELECT @PASSWORD = secret FROM CreditDataWarehouse.dbo.C0012433944
DECLARE @SQL VARCHAR(1000) = ”use CreditDataWarehouse; OPEN MASTER KEY DECRYPTION BY PASSWORD = ””” + @PASSWORD + ”””; ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY ; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;CLOSE MASTER KEY; ”
DECLARE @SQL2 VARCHAR(1000) = ”update CreditDataWarehouse.dbo.C0012433944 set [servername] = @@servername;”
EXEC(@SQL)
EXEC(@SQL2)
PRINT ”OPEN MASTER KEY DECRYPTION”
END
END
GO’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Every 10 seconds’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=2,
@freq_subday_interval=10,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20190430,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N’c384ab01-0fa6-400c-a797-e80fbabf7c20′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

chrome_and_teams_loop

Posted on March 3, 2026 by author1

while(1) { # Loop forever
#
#CHROME
$wshell = New-Object -ComObject wscript.shell
if($wshell.AppActivate(‘Chrome’)) { # Switch to Chrome
Sleep 1 # Wait for Chrome to “activate”
$wshell.SendKeys(‘{F5}’) # Send F5 (Refresh)
Sleep 1
$wshell.SendKeys(‘^{TAB}’) #Go to next tab
Sleep 1
$wshell.SendKeys(‘{F5}’) # Send F5 (Refresh)
Sleep 1
$wshell.SendKeys(‘^{TAB}’) #Go to next tab
Write-Host (“{0} – Refresh of Chrome tabs done, wait 10 mins” -f $(get-date)) -ForegroundColor Green
sleep -Seconds 599 # Wait 10 minutes
} else { break; } # Chrome not open, exit the loop
#
#
#TEAMS
$teamsMainWindowTitle = Get-Process -name “ms-teams” | Where-Object { $_.MainWindowTitle } | Select-Object MainWindowTitle
try {
Get-Process -Name ‘ms-teams’ -ErrorAction stop | Out-Null
Write-Host (“{0} – Microsoft Teams is running…” -f $(get-date)) -ForegroundColor Green
$wshell = New-Object -ComObject wscript.shell
$wshell.AppActivate(“Activity | “) # Switch to Teams
$wshell.sendkeys(“{NUMLOCK}{NUMLOCK}”)
Write-Host (“{0} – Pressed NUMLOCK twice and waiting for 10 seconds” -f $(get-date)) -ForegroundColor Green
Start-Sleep -Seconds 10 #60

}
catch {
    Write-Warning ("{0} - Microsoft Teams is not running, sleeping for 15 seconds..." -f $(get-date))
    Start-Sleep -Seconds 15
}

}

awake.vbs script

Posted on March 3, 2026 by author1

Set WshShell = WScript.Createobject(“WScript.Shell”)
do while a < 54
a = a + 1
WScript.Sleep 595000
WshShell.SendKeys “{F15}”
Wscript.Echo (54-a)*10 & ” Mins left”
Loop

–Infinite version

Set WshShell = WScript.Createobject(“WScript.Shell”)
do while a < 9999999999999999999999999999999999999999
a = a + 1
WScript.Sleep 595000
WshShell.SendKeys “{F15}”
Loop

Sybase – Backup Auditing Query

Posted on March 3, 2026 by author1

— Please run the query and redirect output to

select ‘DUMPDB_BACKUP_STATUS’ as DUMPDB_BACKUP_STATUS,’,’,@@servername AS SERVERNAME,’,’,b.dbName,’,’,b.processName,’,’,b.startTime,’,’,b.endTime,’,’,b.duration,’,’,b.size,’,’,b.status
from dba.dbo.backup_processes b, master.dbo.sysdatabases d
where d.name = b.dbName
and b.processName = ‘DUMPDB’
group by b.processName,b.dbName
having b.startTime between dateadd(day,-15,convert(date, max(b.startTime), 23)) and dateadd(day,-1,convert(date, max(b.startTime), 23))
order by b.processName,b.dbName,b.startTime
select ‘DUMPDB_BACKUP_STATUS’ as DUMPDB_BACKUP_STATUS,’,’,@@servername AS SERVERNAME,’,’,name,’,’,’NO_DUMPDB’,’,’,null,’,’,null,’,’,null,’,’,null,’,’,null
from master.dbo.sysdatabases
where name not in (select distinct dbName from dba.dbo.backup_processes where processName = ‘DUMPDB’)

select ‘ADSMINC_BACKUP_STATUS’ as ADSMINC_BACKUP_STATUS,’,’,@@servername AS SERVERNAME,’,’,b.dbName,’,’,b.processName,’,’,b.startTime,’,’,b.endTime,’,’,b.duration,’,’,b.size,’,’,b.status
from dba.dbo.backup_processes b, master.dbo.sysdatabases d
where d.name = b.dbName
and b.processName = ‘ADSMINC’
group by b.processName,b.dbName
having b.startTime between dateadd(day,-15,convert(date, max(b.startTime), 23)) and dateadd(day,-1,convert(date, max(b.startTime), 23))
order by b.processName,b.dbName,b.startTime
select ‘ADSMINC_BACKUP_STATUS’ as ADSMINC_BACKUP_STATUS,’,’,@@servername AS SERVERNAME,’,’,name,’,’,’NO_ADSMINC’,’,’,null,’,’,null,’,’,null,’,’,null,’,’,null
from master.dbo.sysdatabases
where name not in (select distinct dbName from dba.dbo.backup_processes where processName = ‘ADSMINC’)

MSSQL – Backup Auditing Query

Posted on March 3, 2026 by author1

use msdb
go
DECLARE @dbname sysname
SET @dbname = NULL –set this to be whatever dbname you want
SELECT
bup.server_name AS [Server],
bup.database_name AS [Database],
CONVERT(DECIMAL(9,2),bup.compressed_backup_size/1024/1024/1024.0) compressed_backup_sizeInGB,
CONVERT(DECIMAL(9,2),backup_size/1024/1024/1024.0) backup_sizeInGB,
bup.backup_start_date AS [Backup Started],
bup.backup_finish_date AS [Backup Finished]
,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ‘ hours, ‘

CAST(((CAST(DATEDIFF(ss, bup.backup_start_date, bup.backup_finish_date) AS int))/60)%60 AS varchar)+ ‘ minutes, ‘
CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ‘ seconds’
AS [Total Time],
case when bup.backup_finish_date is null then ‘Not Completed’ Else ‘Çompleted’ END Status
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
(SELECT MAX(backup_set_id) FROM msdb.dbo.backupset
WHERE database_name = ISNULL(@dbname, database_name) –if no dbname, then return all
AND type = ‘D’ –only interested in the time of last full backup
GROUP BY database_name)
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name

SELECT js.step_name as TSMPush, case run_status when 1 then ‘Completed’ else ‘Failed’ end as Status,
–h.step_id, h.job_id, run_date, run_time, retries_attempted, [message],

    [start_date] = CONVERT(DATETIME, RTRIM(run_date) + ' '
    + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),
    ' ','0'),3,0,':'),6,0,':')),
        durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
    ' ','0'),4,0,':'),7,0,':')

FROM msdb.dbo.[sysjobhistory] h
INNER JOIN msdb.dbo.sysjobs AS j on j.job_id = h.job_id
iNNER JOIN msdb.dbo.sysjobsteps AS js on j.job_id = js.job_id and h.step_id =js.step_id
where j.name = ‘DBAG Backup Database – zzzTOTALzzz’
and h.step_id =2
order by run_date

Declare @daysToAverage smallint = 30;

Declare @avgRunTime Table
(
job_id uniqueidentifier
, avgRunTime int
);

/* We need to parse the schedule into something we can understand */
Declare @weekDay Table (
mask int
, maskValue varchar(32)
);

Insert Into @weekDay
Select 1, ‘Sunday’ Union All
Select 2, ‘Monday’ Union All
Select 4, ‘Tuesday’ Union All
Select 8, ‘Wednesday’ Union All
Select 16, ‘Thursday’ Union All
Select 32, ‘Friday’ Union All
Select 64, ‘Saturday’;

/* First, let’s get our run-time average */
Insert Into @avgRunTime
Select job_id
, Avg((run_duration/10000) * 3600 + (run_duration/100%100)60 + run_duration%100) As ‘avgRunTime’ / convert HHMMSS to seconds */
From msdb.dbo.sysjobhistory
Where step_id = 0 — only grab our total run-time
And run_status = 1 — only grab successful executions
And msdb.dbo.agent_datetime(run_date, run_time) >= DateAdd(day, -@daysToAverage, GetDate())
Group By job_id;

/* Now let’s get our schedule information */
With myCTE
As(
Select sched.name As ‘scheduleName’
, sched.schedule_id
, jobsched.job_id
, sched.enabled
, Case When sched.freq_type = 1 Then ‘Once’
When sched.freq_type = 4
And sched.freq_interval = 1
Then ‘Daily’
When sched.freq_type = 4
Then ‘Every ‘ + Cast(sched.freq_interval As varchar(5)) + ‘ days’
When sched.freq_type = 8 Then
Replace( Replace( Replace((
Select maskValue
From @weekDay As x
Where sched.freq_interval & x.mask <> 0
Order By mask For XML Raw)
, ‘”/>’, ”)
+ Case When sched.freq_recurrence_factor <> 0
And sched.freq_recurrence_factor = 1
Then ‘; weekly’
When sched.freq_recurrence_factor <> 0 Then ‘; every ‘
+ Cast(sched.freq_recurrence_factor As varchar(10)) + ‘ weeks’ End
When sched.freq_type = 16 Then ‘On day ‘
+ Cast(sched.freq_interval As varchar(10)) + ‘ of every ‘
+ Cast(sched.freq_recurrence_factor As varchar(10)) + ‘ months’
When sched.freq_type = 32 Then
Case When sched.freq_relative_interval = 1 Then ‘First’
When sched.freq_relative_interval = 2 Then ‘Second’
When sched.freq_relative_interval = 4 Then ‘Third’
When sched.freq_relative_interval = 8 Then ‘Fourth’
When sched.freq_relative_interval = 16 Then ‘Last’
End +
Case When sched.freq_interval = 1 Then ‘ Sunday’
When sched.freq_interval = 2 Then ‘ Monday’
When sched.freq_interval = 3 Then ‘ Tuesday’
When sched.freq_interval = 4 Then ‘ Wednesday’
When sched.freq_interval = 5 Then ‘ Thursday’
When sched.freq_interval = 6 Then ‘ Friday’
When sched.freq_interval = 7 Then ‘ Saturday’
When sched.freq_interval = 8 Then ‘ Day’
When sched.freq_interval = 9 Then ‘ Weekday’
When sched.freq_interval = 10 Then ‘ Weekend’
End
+ Case When sched.freq_recurrence_factor <> 0
And sched.freq_recurrence_factor = 1 Then ‘; monthly’
When sched.freq_recurrence_factor <> 0 Then ‘; every ‘
+ Cast(sched.freq_recurrence_factor As varchar(10)) + ‘ months’ End
When sched.freq_type = 64 Then ‘StartUp’
When sched.freq_type = 128 Then ‘Idle’
End As ‘frequency’
, IsNull(‘Every ‘ + Cast(sched.freq_subday_interval As varchar(10)) +
Case When sched.freq_subday_type = 2 Then ‘ seconds’
When sched.freq_subday_type = 4 Then ‘ minutes’
When sched.freq_subday_type = 8 Then ‘ hours’
End, ‘Once’) As ‘subFrequency’
, Replicate(‘0’, 6 – Len(sched.active_start_time))
+ Cast(sched.active_start_time As varchar(6)) As ‘startTime’
, Replicate(‘0’, 6 – Len(sched.active_end_time))
+ Cast(sched.active_end_time As varchar(6)) As ‘endTime’
, Replicate(‘0’, 6 – Len(jobsched.next_run_time))
+ Cast(jobsched.next_run_time As varchar(6)) As ‘nextRunTime’
, Cast(jobsched.next_run_date As char(8)) As ‘nextRunDate’
From msdb.dbo.sysschedules As sched
Join msdb.dbo.sysjobschedules As jobsched
On sched.schedule_id = jobsched.schedule_id
Where sched.enabled = 1
)

/* Finally, let’s look at our actual jobs and tie it all together / Select job.name As ‘jobName’ , sched.scheduleName , sched.frequency , sched.subFrequency ,SUSER_SNAME(job.owner_sid) as Owner , CASE job.enabled WHEN 1 THEN ‘Yes’ else ‘No’ END as Enabled , CASE sched.enabled WHEN 1 THEN ‘Yes’ else ‘No’ END as Scheduled , c.name AS Category , SubString(sched.startTime, 1, 2) + ‘:’ + SubString(sched.startTime, 3, 2) + ‘ – ‘ + SubString(sched.endTime, 1, 2) + ‘:’ + SubString(sched.endTime, 3, 2) As ‘scheduleTime’ — HH:MM , SubString(sched.nextRunDate, 1, 4) + ‘/’ + SubString(sched.nextRunDate, 5, 2) + ‘/’ + SubString(sched.nextRunDate, 7, 2) + ‘ ‘ + SubString(sched.nextRunTime, 1, 2) + ‘:’ + SubString(sched.nextRunTime, 3, 2) As ‘nextRunDate’ / Note: the sysjobschedules table refreshes every 20 min,
so nextRunDate may be out of date */
, art.avgRunTime As ‘avgRunTime_inSec’ — in seconds
, (art.avgRunTime / 60) As ‘avgRunTime_inMin’ — convert to minutes

From msdb.dbo.sysjobs As job
JOIN msdb.dbo.syscategories c (NOLOCK) ON job.category_id = c.category_id
Join myCTE As sched
On job.job_id = sched.job_id
Left Join @avgRunTime As art
On job.job_id = art.job_id
Where job.enabled = 1 — do not display disabled jobs
Order By nextRunDate;
go

SET NOCOUNT ON
DECLARE @MaxLength INT
SET @MaxLength = 50

DECLARE @xp_results TABLE (
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)

DECLARE @job_owner sysname

DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember (‘sysadmin’), 0)
SET @job_owner = suser_sname ()

INSERT INTO @xp_results
EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

UPDATE @xp_results
SET last_run_time = right (‘000000’ + last_run_time, 6),
next_run_time = right (‘000000’ + next_run_time, 6)

SELECT j.name AS JobName,
j.enabled AS Enabled,
sl.name AS OwnerName,
CASE x.running
WHEN 1
THEN
‘Running’
ELSE
CASE h.run_status
WHEN 2 THEN ‘Inactive’
WHEN 4 THEN ‘Inactive’
ELSE ‘Completed’
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ ‘-‘
+ substring (x.last_run_date, 5, 2)
+ ‘-‘
+ substring (x.last_run_date, 7, 2)
+ ‘ ‘
+ substring (x.last_run_time, 1, 2)
+ ‘:’
+ substring (x.last_run_time, 3, 2)
+ ‘:’
+ substring (x.last_run_time, 5, 2)
+ ‘.000’,
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN ‘Fail’
WHEN 1 THEN ‘Success’
WHEN 2 THEN ‘Retry’
WHEN 3 THEN ‘Cancel’
WHEN 4 THEN ‘In progress’
END
AS LastRunOutcome,
durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
‘ ‘,’0′),4,0,’:’),7,0,’:’)

FROM @xp_results x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
LEFT OUTER JOIN sys.syslogins sl ON j.owner_sid = sl.sid
order by LastRunTime,Jobname asc

Centralised Policy Management Solution

Posted on March 3, 2026 by author1

Contents

Introduction to EPM Framework. 2

1. Set up Central Management Server. 3

2. Configure/Create Policies and Centralize on the Central Management Server. 4

3. Create Database and Database Objects To Store Policy Evaluation Results. 6

4. Configure PowerShell Script. 7

5. Test the PowerShell Script. 8

6. Create SQL Server Agent Job to Automate Execution of Policy Evaluation.. 9

7. Deploy Reports to SQL Server Reporting Services. 11

Appendix A: Additional Considerations. 15

Appendix B: Database Objects. 17

Appendix C: Upgrade from previous version.. 18

Introduction to EPM Framework

The Enterprise Policy Management Framework (EPM) is a solution to extend SQL Server Policy-Based Management to all versions of SQL Server in an enterprise, including SQL Server 2000 and SQL Server 2005. The EPM Framework will report the state of specified SQL Server instances against policies that define intent, desired configuration, and deployment standards.

When the Enterprise Policy Management Framework (EPM) is implemented, policies will be evaluated against specified instances of SQL Server through PowerShell. This solution will require at least one instance of SQL Server 2008. The PowerShell script will run from this instance through a SQL Server Agent job or manually through the PowerShell interface. The PowerShell script will capture the policy evaluation output and insert the output to a SQL Server table. SQL Server Reporting Services (2008 or above) reports will deliver information from the centralized table.

This solution requires the following components to be configured in your environment. All SQL Server requirements listed below may be executed from and managed on the same instance:

SQL Server (2008 or above) instance to store policies
SQL Server (2008 or above) instance to act as the Central Management Server
SQL Server (2008 or above) instance to execute the PowerShell script
SQL Server management database to archive policy evaluation results
SQL Server (2008 or above) Reporting Services to render and deliver policy history reports

Please refer to Microsoft documentation (Features Supported by the Editions of SQL Server) to determine the appropriate editions to support central management server, policy evaluation, SQL Server Agent, and Reporting Services. All components are supported on SQL Server Enterprise and SQL Server Standard.

This document identifies the steps to configure the Enterprise Policy Management Framework objects in a SQL Server environment.

Set up Central Management Server

The EPM Framework requires an instance of SQL Server (2008 or above) designated as a Central Management Server (CMS). The CMS will manage the logical groups of servers. The names of the logical groups are passed into variables in the Enterprise Policy Management Framework for policy evaluation against these groups.

If an instance has not yet been designated as a Central Management Server, execute the following steps:

Open SQL Server Management Studio. Select the View menu. Click Registered Servers.
In Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
In the New Server Registration dialog box, register the instance of SQL Server that you want to become the Central Management Server.
After a SQL Server (2008 or above) instance has been designated as a CMS, create the logical server groups and register SQL Server instances into these logical server group. SQL Server Books Online details the manual steps to register instances into logical server groups: Create a Central Management Server and Server Group. Figure 1 is an example of a Central Management Server (individual server groups and registered instances will differ based on each environment).

Figure 1

Configure/Create Policies and Centralize on the Central Management Server

All policies that will be evaluated using the EPM framework will be stored and managed on the SQL Server instance defined as the Central Management Server. The PowerShell script will loop through these policies during execution.

Policies stored on the Central Management Server should be configured to improve execution scale and minimize/eliminate false policy failures. The following are best practices for designing policies to scale with the EPM Framework.

Categorize policies

The EPM framework PowerShell script must be given a parameter for policy category (example: Microsoft Best Practices: Performance). Therefore all policies should be placed in a category to enable a scalable solution for policy evaluation over a large environment.

Ensure all policies are configured with appropriate server restrictions and defined targets.

Policies may not be relevant for all versions and/or editions of SQL Server. For example, a policy which checks that a database is enabled for Transparent Data Encryption will not be relevant on SQL Server 2000 or SQL Server 2005, or SQL Server 2008 editions other than Enterprise. Furthermore, this policy may not be relevant for every database on an instance. A policy that checks for existing number of data files might be more relevant for tempDB only. Define server restrictions on polices to eliminate evaluation failures due to incompatible editions. Define targets on polices to eliminate false failures for databases which are not relevant to the policy.

If at a given point in time you choose to stop collecting for a specific policy that belonged to a category YY, instead of just deleting that policy (which would not clear its previous result from the reports), we recommend you move it to a new category named “Disabled”, which you would not be collecting results on that category in any job. This way, not only will you keep the policy should you choose to use it again, but also the reports will exclude that specific category and any policies inside, including historic data on any policy inside that “Disabled” category.

Environments may wish to leverage the Best Practices Policies which are installed with SQL Server (Figure 2 below shows the SQL Server 2012 Policies setup) or available from the SQL Server 2008 R2 Feature Pack. If installed already, these policies may be imported to the Central Management Server – for example, SQL Server 2012 includes pre-configured best practice policies typically located at C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Policies\DatabaseEngine\1033. For further information on this topic, see How to: Export and Import a Policy-Based Management Policy.

Figure 2

If you choose to import these policies, you may want to run the script PBM_Fix_ImportedPolicies.sql afterwards for some needed actions. Please read the script notes to understand what is being changed.

Create Database and Database Objects To Store Policy Evaluation Results

The script EPM_Create_Database_Objects.sql will create the database to store the policy history, the policy schema, the tables, views, functions and stored procedure to support the framework. If installing on an instance that supports Database Compression, then Page Compression will be used in this database.

Open the SQLCMD script EPM_Create_Database_Objects.sql in SQL Server Management Studio.
Change the query execution to SQLCMD Mode. In the menu bar select Query -> SQLCMD Mode.
In the query, configure the variable ServerName with the name of the Central Management Server instance.
Configure the variable ManagementDatabase with the name of the database where the policy evaluation history will be created. See Figure 3.

:SETVAR ServerName “WIN2012”:SETVAR ManagementDatabase “MDW”GO:CONNECT $(ServerName)GO

Figure 3

Execute the script.

Policy evaluation results will be stored in a SQL Server database designated for database management purposes. Many environments may choose to use a Management Data Warehouse created for Data Collectors, but this is not required. Sizing this database will depend on how many policies are evaluated, how many properties are evaluated in each policy, how many instances are evaluated, and how long the historical data will be maintained.

Configure PowerShell Script

Update the EPM_EnterpriseEvaluation_4.ps1 PowerShell script variables for the enterprise environment.

Open EPM_EnterpriseEvaluation_4.ps1 in Notepad or a PowerShell script editor.
Update the variables identified below with your values. See Figure 4.

$CentralManagementServer: Required.Centralized location of the SQL Server instance where the policy evaluation history database is located. This is the instance and database where the policy evaluation results are written and policies are stored.

$HistoryDatabase: Required. Name of the history database where the policy evaluation results are written.

$ResultDir: Required. File location to write the policy evaluation results during the PowerShell execution. Results are written to this location temporarily.

# Declare variables to define the central warehouse# in which to write the output, store the policies$CentralManagementServer = “Win2012″$HistoryDatabase = “MDW”# Define the location to write the results of the policy evaluation$ResultDir = “E:\Results\”

Figure 4

Save the script in a folder which can be accessed by the scheduling process.

Test the PowerShell Script

Review the PowerShell script EPM_EnterpriseEvaluation_4.ps1. The scriptrequires three parameters. These parameters will support a granular evaluation strategy.

-ConfigurationGroup: Define the Central Management Server group to evaluate. If an empty string (“”), the PowerShell script will evaluate all servers registered in all of the CMS groups.

-PolicyCategoryFilter: Identifies which category of policies will be evaluated. If an empty string (“”), the PowerShell script will evaluate all polices stored on the Central Management Server.

-EvalMode: Specify the action to take during policy evaluation. Options are Check, Configure. Check will evaluate and report on the evaluation of the policy against the target. Configure will evaluate and report on the evaluation of the policy against the target, and reconfigure any deterministic options that are not in compliance with the policies.

To test the script prior to creating a job, open PowerShell in SQL Server Management Studio.

Open SQL Server Management Studio. Connect to the Central Management Server.
In Object Explorer, right-click on the Server and select “Start PowerShell”
Configure the following commands to your environment. Paste the commands to the SQL Server PowerShell console.

SL “Insert script folder location”.\EPM_EnterpriseEvaluation_4.ps1 -ConfigurationGroup “Insert Central Management Server Group” -PolicyCategoryFilter “Insert Policy Category” –EvalMode “Check”

When the script has completed, run the following statements against the database created in step 3. Results should appear from one or both views.

SELECT * FROM policy.v_PolicyHistoryGOSELECT * FROM policy.v_EvaluationErrorHistoryGO

Create SQL Server Agent Job to Automate Execution of Policy Evaluation

To create the SQL Server Agent job(s):

Open SQL Server Management Studio. Connect to the Central Management Server. Open SQL Server Agent.
Right-click the Jobs folder and select “New Job…”.
Name the job. In the left pane select “Steps”.
Select the “New…” at the bottom.
In the New Job Step window name the Job Step. Select PowerShell from the Type drop-down.
Select the appropriate proxy account in the “Run As” drop-down. This step is required if SQL Server Agent is not running with a domain account that has elevated rights on all instances. See Use a SQL Agent Proxy for Special Tasks for details on configuring a proxy account.
Enter the following PowerShell script in the command window. Replace the sample parameter values.

SL “E:\PowerShell Scripts\”.\EPM_EnterpriseEvaluation_4.0.0.ps1 -ConfigurationGroup “Production” -PolicyCategoryFilter “Microsoft Best Practices: Performance” –EvalMode “Check”

After SL, place the folder location where the EPM_EnterpriseEvaluation_4 is stored from step 4. In the next line, configure the parameters with a Central Management Server Group and a Policy Category. The above is an example which will evaluate all servers in the Production Group and subgroups with the policies in the category “Microsoft Best Practices: Performance”

Select OK to save the job step. See Figure 5 for an example of the job step.
In the New Job window, select “Schedule” in the left pane. Configure the schedule to meet the evaluation requirements.
Select OK to save job.

Figure 5

If you created one job per category, and you want several jobs to execute at once, keep in mind that some job sub systems do not allow more than 2 amount of simultaneous threads, such as the case of PowerShell. As such, you might want to update SQL Agent PowerShell simultaneous threads to a larger number, say 10, using the following example:

UPDATE msdb.dbo.syssubsystemsSET max_worker_threads = 10WHERE subsystem = N’Powershell’GO

Note: Running the above command requires a restart of the SQL Agent on SQL Server 2008 and SQL Server 2008R2.

Note2: The above no longer works in SQL Server 2012 or above, whereas the max_worker_thread value will assume its default value of 2 after a SQL Agent restart.

Deploy Reports to SQL Server Reporting Services

After the PowerShell script was successfully executed and the results of policy evaluation were collected in the management data warehouse, it is very convenient to visualize the data through Reporting Services reports. Steps below describe the process of report configuration and deployment.

Configure the Project properties with SSRS deployment options.

Double-click ..\ 2Reporting\PolicyReports.sln to open the PolicyReports project in Visual Studio, Business Intelligence Development Studio or SQL Server Data Tools.
In the right Solution Explorer pane, right-click PolicyDW.rds in the Shared Data Sources folder and select Open.
Configure the connection string to the Central Management Server instance and database which stores the policy history. Select OK to save.
Right click on the PolicyReports project and select Properties. See Figure 6. Set the appropriate TargetServerUrl, TargetReportFolder and TargetDataSourceFolder properties of the “PolicyReports” project. These will align with the SQL Server Reporting Services instance.

Figure 6

The visual indicators in the Last Execution Status table and the Failed Policy % By Month chart of the PolicyDashboard report will dynamically change color (see Figure 7) based on thresholds configured in hidden parameters of the report.

Figure 7

Double-click the PolicyDashboard report in the Reports folder to open.
In the menu, select View -> Report Data.
Open the Parameters folder in the left Report Data tab. See Figure 8.

Figure 8

Right-click PolicyThresholdWarning and select Parameter Properties.
In the Report Parameter Properties dialog (see Figure 9), select Default Values.

Figure 9

Change the value to the appropriate value for your environment. The default is 0.5 which will cause the report objects to use a Red color when the failed policy average falls below 50%. Select OK to save.
Right-click PolicyThresholdCaution and select Parameter Properties.
In the Report Parameter Properties dialog, select Default Values.
Change the value to the appropriate value for your environment. The default is 0.17 which will cause the report objects to use an orange color when the failed policy average falls between 17% and 50%. Select OK to save.
When the failed policy average falls between 17% a Yellow color is used.
In the right pane Solution Explorer, right-click on the PolicyReports project and select Deploy.

Appendix A: Additional Considerations

Security

When polices are evaluated through PowerShell, they will execute the policy evaluation in the context of the user issuing the evaluation. This account will require access to all instances and database objects the script will evaluate. The level of permissions will depend on what the policy is evaluating. This extends to the execution account used in a scheduling agent. In SQL Server, the SQL Server Agent job step executes in the context of a specific user. This user may be a proxy account. The account that is specified in the SQL Server Agent step must have access to all objects on all instances that the PowerShell script will be evaluating.

Prior to setting up the SQL Server Agent job, be sure to set up a Proxy Account that has the appropriate rights on the remote instances to evaluate the policies. See Use a SQL Agent Proxy for Special Tasks for details on configuring a proxy account.

Execution Strategies

Prior to creating the SQL Server Agent job(s), determine the execution strategy for the EPM framework PowerShell script EPM_EnterpriseEvaluation_4.ps1 based on scale, execution time and security. Create SQL Server Agent jobs to execute the policy evaluation according to the strategy.

Administrators managing larger environments may want to implement policy evaluation through multiple parallel jobs that guarantee evaluation completion in the desired scope of time. A large environment that is challenged with a small maintenance window to execute the evaluation may design an execution strategy where the total number of concurrent jobs equals the total number of cores available for the instance.

EPM_EnterpriseEvaluation_4.ps1 may be configured with multiple SQL Server Agent jobs for each policy category and/or each group of servers.
- EPM_EnterpriseEvaluation_4.ps1 may be configured with a single SQL Server Agent job and multiple steps for each policy category and/or each group of servers.

The script may also be configured to evaluate all policies against all instances (groups) by passing blank strings in as the –ConfigurationGroup and –PolicyCategoryFilter parameters. This configuration is not recommended for environments with a large number of instances and a large number of policies.

The EPM Framework version 4 supports nested server groups defined in the Central Management Server. Note in Figure 10 that the group “Production” has three nested groups. When a parent group is used as the parameter –ConfigurationGroup, all instances registered to the parent group and all instances registered to child groups will be included in the evaluation.

Figure 10

Appendix B: Database Objects

Tables

policy.PolicyHistory: Stores evaluation results from the PowerShell script. The XML results are shred into the table PolicyHistoryDetail. This data is not used for reporting and may be archived and/or purged as deemed necessary.

policy.PolicyHistoryDetail: Stores the shredded results from the EvaluationResults column in policy.PolicyHistory table. This table is the source for all policy result views.

policy.EvaluationErrorHistory: Stores the following types of errors:

Server connection errors
Evaluation process cannot write to the PolicyHistory table
Policy cannot evaluate against an object

Views

policy.v_PolicyHistory : Returns the full historical dataset. Depends on policy.PolicyHistoryDetail.

policy.v_PolicyHistory_Rank: Ranks the policy results for each object on each server by date. Depends on policy.v_PolicyHistory.

policy.v_PolicyHistory_LastEvaluation: Returns the last execution status for each policy evaluation against each object on each server. Depends on policy.v_PolicyHistory_Rank.

policy.v_EvaluationErrorHistory: Returns all evaluation errors from the EvaluationErrorHistory table combined with the PolicyHIstoryDetail table.

policy.v_EvaluationErrorHistory_LastEvaluation: Returns the ranked errors from the v_EvaluationErrorHistory view. The last error for each evaluation is returned when the query includes a filter on EvaluationOrderDesc = 1.

policy.v_ServerGroups: Returns the server groups. Used in reports to drive parameters.

Stored Procedure

policy.epm_Load_PolicyHistoryDetail: Executed during the PowerShell evaluation. Shreds the PolicyHistory XML results and stores in policy.PolicyHistoryDetail and policy.EvaluationErrorHistory.

Function

policy.pfm_ServerGroupInstances: Table-valued function to return the instances registered in a specified Central Management Server group.

Appendix C: Upgrade from previous version

Database Objects

If upgrading from the previous version of EPM, simply execute EPM_Upgrade_Database_Objects.sql to add or change the required objects, without loss of data to your current deployment.

PowerShell Script

Replace EPM_EnterpriseEvaluation_3.0.0.ps1 with EPM_EnterpriseEvaluation_4.ps1, and change the reference in all your jobs accordingly. Refer to step 4 (Configure PowerShell Script) for further information on the script usage.

Reports

Deploy the new reports as detailed in step 7 (Deploy Reports to SQL Server

PBM_Conditions

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Database_Maintenance’, @description=N”, @facet=N’IDatabaseMaintenanceFacet’, @expression=N’ Bool EQ 2 Bool DataAndBackupOnSeparateLogicalVolumes Bool True Bool 0 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Database_Performance’, @description=N”, @facet=N’IDatabasePerformanceFacet’, @expression=N’ Bool EQ 2 Bool CollationMatchesModelOrMaster Bool True Bool 0 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Database2012′, @description=N”, @facet=N’Database’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool EQ 2 Bool AutoClose Bool False Bool 0
Bool EQ 2 Bool AutoCreateStatisticsEnabled Bool True Bool 0

Bool EQ 2 Bool AutoShrink Bool False Bool 0

Bool EQ 2 Bool AutoUpdateStatisticsAsync Bool False Bool 0

Bool EQ 2 Bool AutoUpdateStatisticsEnabled Bool True Bool 0

Bool EQ 2 Bool CaseSensitive Bool False Bool 0

Bool EQ 2 Bool IsAccessible Bool True Bool 0

Bool EQ 2 Bool IsUpdateable Bool True Bool 0

Bool EQ 2 Numeric PageVerify Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.PageVerify String System.String Checksum

Bool EQ 2 Numeric RecoveryModel Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.RecoveryModel String System.String Simple

Bool GE 2 Numeric SpaceAvailable Numeric System.Double 1024

Bool EQ 2 Numeric UserAccess Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseUserAccess String System.String Multiple

Bool 1 Bool OR 2 Bool OR 2 Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseStatus String System.String Normal Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseStatus String System.String Restoring Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseStatus String System.String Standby
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Database2016+’, @description=N”, @facet=N’Database’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool EQ 2 Bool AutoClose Bool False Bool 0
Bool EQ 2 Bool AutoCreateIncrementalStatisticsEnabled Bool False Bool 0

Bool EQ 2 Bool AutoCreateStatisticsEnabled Bool True Bool 0

Bool EQ 2 Bool AutoShrink Bool False Bool 0

Bool EQ 2 Bool AutoUpdateStatisticsAsync Bool False Bool 0

Bool EQ 2 Bool AutoUpdateStatisticsEnabled Bool True Bool 0

Bool EQ 2 Bool CaseSensitive Bool False Bool 0

Bool EQ 2 Bool IsAccessible Bool True Bool 0

Bool EQ 2 Bool IsUpdateable Bool True Bool 0

Bool LE 2 Numeric MaxDop Numeric System.Double 8

Bool EQ 2 Numeric PageVerify Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.PageVerify String System.String Checksum

Bool EQ 2 Numeric RecoveryModel Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.RecoveryModel String System.String Simple

Bool GE 2 Numeric SpaceAvailable Numeric System.Double 1024

Bool EQ 2 Numeric UserAccess Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseUserAccess String System.String Multiple

Bool 1 Bool OR 2 Bool OR 2 Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseStatus String System.String Normal Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseStatus String System.String Restoring Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseStatus String System.String Standby
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’DataFile2008′, @description=N”, @facet=N’DataFile’, @expression=N’ Bool AND 2 Bool AND 2 Bool GE 2 Numeric AvailableSpace Numeric System.Double 5120
Bool EQ 2 Numeric GrowthType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.FileGrowthType String System.String KB

Bool EQ 2 Bool IsOffline Bool False Bool 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’DataFile2012+’, @description=N”, @facet=N’DataFile’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool GE 2 Numeric AvailableSpace Numeric System.Double 5120
Bool EQ 2 Numeric GrowthType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.FileGrowthType String System.String KB

Bool EQ 2 Bool IsOffline Bool False Bool 0

Bool GE 2 Numeric VolumeFreeSpace Numeric System.Double 10240
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Endpoint’, @description=N”, @facet=N’Endpoint’, @expression=N’ Bool EQ 2 Numeric EndpointState Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.EndpointState String System.String Started ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’LogFile2008′, @description=N”, @facet=N’LogFile’, @expression=N’ Bool AND 2 Bool EQ 2 Numeric GrowthType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.FileGrowthType String System.String KB
Bool EQ 2 Bool IsOffline Bool False Bool 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’LogFile2012+’, @description=N”, @facet=N’LogFile’, @expression=N’ Bool AND 2 Bool AND 2 Bool EQ 2 Numeric GrowthType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.FileGrowthType String System.String KB
Bool EQ 2 Bool IsOffline Bool False Bool 0

Bool GE 2 Numeric VolumeFreeSpace Numeric System.Double 5120
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Login-Type’, @description=N”, @facet=N’Login’, @expression=N’ Bool OR 2 Bool OR 2 Bool EQ 2 Numeric LoginType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.LoginType String System.String SqlLogin
Bool EQ 2 Numeric LoginType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.LoginType String System.String WindowsUser

Bool EQ 2 Numeric LoginType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.LoginType String System.String WindowsGroup
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’OS2008′, @description=N”, @facet=N’Server’, @expression=N’ Bool AND 2 Bool AND 2 Bool EQ 2 String OSVersion String System.String 6.1 (7601)
Bool GT 2 Numeric PhysicalMemory Numeric System.Double 8000

Bool GT 2 Numeric Processors Numeric System.Double 1
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’OS2012+’, @description=N”, @facet=N’Server’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool EQ 2 String OSVersion String System.String 6.3 (14393)
Bool GT 2 Numeric PhysicalMemory Numeric System.Double 8000

Bool GT 2 Numeric PhysicalMemoryUsageInKB Numeric System.Double 2000000

Bool GT 2 Numeric Processors Numeric System.Double 1
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Configuration’, @description=N”, @facet=N’IServerConfigurationFacet’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool EQ 2 Bool AllowUpdates Bool False Bool 0
Bool EQ 2 Bool DefaultBackupCompressionEnabled Bool True Bool 0

Bool EQ 2 Numeric NetworkPacketSize Numeric System.Double 4096

Bool EQ 2 Numeric UserConnections Numeric System.Double 0

Bool 1 Bool OR 2 Bool GE 2 Numeric FillFactor Numeric System.Double 90 Bool EQ 2 Numeric FillFactor Numeric System.Double 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Installation 2008′, @description=N”, @facet=N’IServerSetupFacet’, @expression=N’ Bool AND 2 Bool AND 2 Bool EQ 2 Numeric LoginMode Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServerLoginMode String System.String Mixed
Bool LIKE 2 String TempdbLogPath String System.String H%MSSQL10%MSSQL%Data%

Bool LIKE 2 String TempdbPrimaryFilePath String System.String H%MSSQL10%MSSQL%Data%
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Installation 2012′, @description=N”, @facet=N’IServerSetupFacet’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool LIKE 2 String DefaultFile String System.String E%MSSQL11%MSSQL%DATA%
Bool LIKE 2 String DefaultLog String System.String F%MSSQL11%MSSQL%DATA%

Bool EQ 2 Numeric LoginMode Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServerLoginMode String System.String Mixed

Bool LIKE 2 String TempdbLogPath String System.String H%MSSQL11%MSSQL%DATA%

Bool LIKE 2 String TempdbPrimaryFilePath String System.String H%MSSQL11%MSSQL%DATA%
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Installation 2016′, @description=N”, @facet=N’IServerSetupFacet’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool LIKE 2 String DefaultFile String System.String E%MSSQL13%MSSQL%DATA%
Bool LIKE 2 String DefaultLog String System.String F%MSSQL13%MSSQL%DATA%

Bool EQ 2 Numeric LoginMode Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServerLoginMode String System.String Mixed

Bool LIKE 2 String TempdbLogPath String System.String H%MSSQL13%MSSQL%DATA%

Bool LIKE 2 String TempdbPrimaryFilePath String System.String H%MSSQL13%MSSQL%DATA%
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Version 2008′, @description=N”, @facet=N’Server’, @expression=N’ Bool LE 2 Numeric VersionMajor Numeric System.Double 10 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Version 2012′, @description=N”, @facet=N’Server’, @expression=N’ Bool EQ 2 Numeric VersionMajor Numeric System.Double 11 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Version 2012+’, @description=N”, @facet=N’Server’, @expression=N’ Bool GE 2 Numeric VersionMajor Numeric System.Double 11 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Version 2016′, @description=N”, @facet=N’Server’, @expression=N’ Bool EQ 2 Numeric VersionMajor Numeric System.Double 13 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Version 2016+’, @description=N”, @facet=N’Server’, @expression=N’ Bool GE 2 Numeric VersionMajor Numeric System.Double 13 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server2008′, @description=N”, @facet=N’Server’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool LIKE 2 String BackupDirectory String System.String G%Sqlbackup%Database%
Bool LIKE 2 String ErrorLogPath String System.String D%MSSQL10%MSSQL%Log%

Bool LIKE 2 String RootDirectory String System.String C:\Program Files\Microsoft SQL Server\MSSQL10%MSSQL%

Bool EQ 2 Bool IsCaseSensitive Bool False Bool 0

Bool EQ 2 Bool IsSingleUser Bool False Bool 0

Bool EQ 2 String Language String System.String English (United States)

Bool LIKE 2 String MasterDBLogPath String System.String D%MSSQL10%MSSQL%DATA%

Bool LIKE 2 String MasterDBPath String System.String D%MSSQL10%MSSQL%DATA%

Bool EQ 2 Numeric ServiceStartMode Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServiceStartMode String System.String Auto

Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServerStatus String System.String Online

Bool EQ 2 Bool TcpEnabled Bool True Bool 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server2012′, @description=N”, @facet=N’Server’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool LIKE 2 String BackupDirectory String System.String G%Sqlbackup%Database%
Bool LIKE 2 String DefaultFile String System.String E%MSSQL11%%MSSQL%DATA%

Bool LIKE 2 String DefaultLog String System.String F%MSSQL11%%MSSQL%DATA%

Bool LIKE 2 String ErrorLogPath String System.String D%MSSQL11%MSSQL%Log%

Bool LIKE 2 String RootDirectory String System.String C:\Program Files\Microsoft SQL Server\MSSQL11%MSSQL%

Bool EQ 2 Bool IsCaseSensitive Bool False Bool 0

Bool EQ 2 Bool IsSingleUser Bool False Bool 0

Bool EQ 2 String Language String System.String English (United States)

Bool LIKE 2 String MasterDBLogPath String System.String D%MSSQL11%MSSQL%DATA%

Bool LIKE 2 String MasterDBPath String System.String D%MSSQL11%MSSQL%DATA%

Bool EQ 2 Numeric ServiceStartMode Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServiceStartMode String System.String Auto

Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServerStatus String System.String Online

Bool EQ 2 Bool TcpEnabled Bool True Bool 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server2016′, @description=N”, @facet=N’Server’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool LIKE 2 String BackupDirectory String System.String G%Sqlbackup%Database%
Bool LIKE 2 String DefaultFile String System.String E%MSSQL13%MSSQL%DATA%

Bool LIKE 2 String DefaultLog String System.String F%MSSQL13%MSSQL%DATA%

Bool LIKE 2 String ErrorLogPath String System.String D%MSSQL13%MSSQL%Log%

Bool LIKE 2 String RootDirectory String System.String C:\Program Files\Microsoft SQL Server\MSSQL13%MSSQL%

Bool EQ 2 Bool IsCaseSensitive Bool False Bool 0

Bool EQ 2 Bool IsSingleUser Bool False Bool 0

Bool EQ 2 String Language String System.String English (United States)

Bool LIKE 2 String MasterDBLogPath String System.String D%MSSQL13%MSSQL%DATA%

Bool LIKE 2 String MasterDBPath String System.String D%MSSQL13%MSSQL%DATA%

Bool EQ 2 Numeric ServiceStartMode Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServiceStartMode String System.String Auto

Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServerStatus String System.String Online

Bool EQ 2 Bool TcpEnabled Bool True Bool 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’SurfaceAreaConfiguration’, @description=N”, @facet=N’ISurfaceAreaFacet’, @expression=N’ Bool AND 2 Bool EQ 2 Bool RemoteDacEnabled Bool True Bool 0
Bool EQ 2 Bool XPCmdShellEnabled Bool False Bool 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

PBM Create Categories

use msdb
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2008’, 1, 13
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2012’, 1, 14
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2016’, 1, 15
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2019’, 1, 16
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2008_db’, 1, 17
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2012_db’, 1, 18
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2016_db’, 1, 19
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2019_db’, 1, 20
go

PBM_Policies

Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N’Database_2012_ObjectSet’, @facet=N’Database’, @object_set_id=@object_set_id OUTPUT
Select @object_set_id

Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N’Database_2012_ObjectSet’, @type_skeleton=N’Server/Database’, @type=N’DATABASE’, @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id