SQL Jobs change job step proxy

The purpose of this script is to set a proxy on each job step to allow the owner of the job to not have access to the target database. This applies when a separate server is used for ETL and data storage. This also has a strong effect on the performance of the database server as significantly less memory has to be allocated to the Integration Services service.

This assume you can segregate the jobs by job category to make sure only the targeted step are changed. This could also be done easily by job owner.


USE MSDB
GO

SELECT * FROM sysproxies;
--Use this to restrict the jobs to only the categories you want to change
DECLARE @categoryMatch nvarchar(30) = 'test_%';
--Set the proxy value to the values desired in the above query
DECLARE @proxy_value nvarchar(10) = 2;
DECLARE @CurJobId nvarchar(50);
DECLARE @curStepId nvarchar(50);

SELECT sj.job_id, sjs.step_id, proxy_id
FROM sysjobs sj
INNER JOIN sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE category_id IN ( select category_id FROM syscategories WHERE  name like @categoryMatch)
AND (sjs.proxy_id != @proxy_value OR proxy_id IS NULL);

DECLARE db_cursor CURSOR FOR
SELECT sj.job_id, sjs.step_id
FROM sysjobs sj
INNER JOIN  sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE  category_id IN (SELECT category_id FROM syscategories WHERE  name like @categoryMatch)
AND (sjs.proxy_id != @proxy_value OR proxy_id IS NULL);


OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @CurJobId, @curStepId

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Job_ID:  ' + @curJobId + '     Job Step:  ' + @curStepId;
EXEC dbo.sp_update_jobstep
@job_id = @curJobId
,@step_id = @curStepId
,@proxy_id = @proxy_value;

    FETCH NEXT FROM db_cursor INTO @CurJobId, @curStepId
END

CLOSE db_cursor
DEALLOCATE db_cursor

Best update database owner tools

In my never ending quest to automate the setting of DB owners here is the best script yet. It assumes there is a user called [Domain User]/[Server name]Server running the server.

USE Master
GO

DECLARE @userName nvarchar(50);
DECLARE @userNamePostfix nvarchar(10) = 'Server'; --'Admin';

IF  CHARINDEX('\', @@SERVERNAME, 0) > 0
SET @userName = '[Domain User]\' +  SUBSTRING(@@SERVERNAME, 0, CHARINDEX('\', @@SERVERNAME, 0)) + @userNamePostfix ;
ELSE
SET @userName = '[Domain User]\' +  @@SERVERNAME + @userNamePostfix;

--DECLARE @userName nvarchar(50) = '[Domain User]\' +  SUBSTRING(@@SERVERNAME, 0, CHARINDEX('\', @@SERVERNAME, 0)) + 'Admin';
-- Use this section to override if the pattern does not work
--SET @userName = 'override User';

PRINT @userName;

--Require the user to exist and be a sysadmin
IF EXISTS(SELECT sid FROM sys.syslogins WHERE name = @userName AND sysAdmin = 1)
BEGIN

--Should be modified to ignore DBs which are not assigned to a defaulte value
DECLARE @prefix  nvarchar(200) = 'IF ''?'' NOT IN (SELECT name from sys.databases WHERE owner_sid = SUSER_ID(''' + @userName + ''') OR DB_NAME(database_id) IN (''master'', ''msdb'',''model'', ''tempdb'', ''distribution'')) BEGIN  ';
DECLARE @postfix  nvarchar(50) = ' END';
DECLARE @cmd  nvarchar(512);

SET @cmd =  @prefix + 'PRINT ''?''; USE ?  exec sp_changedbowner @loginame=''' + @userName + ''' ' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Successfully set DB owner to ' + @userName;
END
ELSE
BEGIN
  PRINT @userName + ' NOT found';
END

Fix for Chrome in Reporting Services 2012 and 2008R2

Add the script below to the file:
In 2008R2
...\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Pages\Report.aspx

In 2012
...\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportManager\Pages\Report.aspx

Find the Number of Rows in Database Tables

Had a need to track empty tables in a DB to see if converted systems were correctly hooked up and wanted to add filters.Sorry I didn't keep the link to the original website.

SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sdmvPTNS.row_count) AS [RowCount]
FROM sys.objects AS sOBJ
INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
ON sOBJ.object_id = sdmvPTNS.object_id
WHERE 
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND sdmvPTNS.index_id < 2
--Find all by schema
-- AND SCHEMA_NAME(sOBJ.schema_id) = ''
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
--Only find empty tables
--HAVING SUM(sdmvPTNS.row_count) = 0
ORDER BY [TableName]
, [RowCount];
GO

Add Log Percent Used Alert to all non System DBs

in my never ending quest to automate more monitoring I have added this to my pre-production deployment package. It assumes a Operator group of the name DBgroup has been created to transmit the alerts.
Also Database Mail must be set up on the agent.

USE [msdb]
GO

DECLARE @RemoveExisting as bit = 1; --Set to 1 to purge any existing

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('model','master', 'msdb','tempdb')

DECLARE @DBname nvarchar(max) = '';
DECLARE @AlertName nvarchar(max) = '';
DECLARE @AlertText nvarchar(max) = '';

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @AlertName = @DBname  + N'_LogPercentUsed';
SET @AlertText = N'Databases|Percent Log Used|' + @DBname + '|>|90';

IF EXISTS(select id from sysalerts where name = @AlertName) AND  @RemoveExisting = 1
EXEC msdb.dbo.sp_delete_alert @AlertName;

IF NOT EXISTS(select id from sysalerts where name = @AlertName)
BEGIN
EXEC msdb.dbo.sp_add_alert @name= @AlertName ,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=1715,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition= @AlertText
, @job_id=N'00000000-0000-0000-0000-000000000000';

EXEC msdb.dbo.sp_add_notification @alert_name= @AlertName, @operator_name=N'DBGroup', @notification_method = 1;

END

  FETCH NEXT FROM db_cursor INTO @DBname
END

CLOSE db_cursor
DEALLOCATE db_cursor

Fail a jobs is any pre-requisite jobs have failed

USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
Used to detemine if any jobs in a category have failed
All jobs must be assigned to the same category

Parameters:
@JobCategoryName - The category name set up in hte local instance
@HoursToCheck - the number of hours back in history you want the call to look. DEFAULT = 24
@IsSuccessful - Whether or not any jobs meet the criteria
To fine all failed jobs put 0

RETURNS
0 = No Jobs in the tested status
1 = Jobs exist in the tested status

Example of use to fail a job when any jobs in the category have a failure
IF (SELECT [dbo].[CheckJobCategoryStatus] ('SAP_to_staging', 24, 0)) = 1
  BEGIN
    RAISERROR('SAP_to_staging pre-requisite has failed. Re-run all failed prerequisites', 16, 1)
  END
*/

CREATE FUNCTION [dbo].[CheckJobCategoryStatus]
(@JobCategoryName nvarchar(50)
, @HoursToCheck int = 24
, @IsSuccessful bit = 0
)
RETURNS bit
AS 
BEGIN
--DECLARE @JobCatagory nvarchar(50) = 'SAP_to_staging';
--DECLARE @HoursToCheck int = 24;
--DECLARE @IsSuccessful bit = 1;

DECLARE @IsCategoryStatusTrue bit = 0;

IF EXISTS(SELECT
SJ.NAME AS [Job Name]
,RUN_STATUS AS [Run Status]
,MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) AS [Last Time Job Ran On]
FROM dbo.SYSJOBS SJ
LEFT OUTER JOIN dbo.SYSJOBHISTORY JH
ON SJ.job_id = JH.job_id
WHERE JH.step_id = 0
AND jh.run_status = @IsSuccessful
and category_id IN (select category_id
from dbo.syscategories
where name = @JobCategoryName)
and DATEDIFF(HH , DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME), getdate()) <= @HoursToCheck
GROUP BY SJ.name, JH.run_status)
SET  @IsCategoryStatusTrue = 1;
ELSE
SET  @IsCategoryStatusTrue = 0;
RETURN @IsCategoryStatusTrue;
END


GO

Monitor Job Satus through script

Had a need to verify prerequisite site had run prior to downstream asynchronous jobs so I found and modified a couple queries.

USE [msdb]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
Used to detemine if any jobs in a category have failed and list them
All jobs must be assigned to the same category

Parameters:
@JobCategoryName - The category name set up in hte local instance
@HoursToCheck - the number of hours back in history you want the call to look. DEFAULT = 24
@IsSuccessful - Whether or not any jobs meet the criteria
To fine all failed jobs put 0

RETURNS
Table with 
Job Name
Success or Failure status
Date Time last run

Example of a table of all failed jobs in a category
SELECT * FROM [dbo].[JobCategoryStatusList] ('SAP_to_staging', 24, 1)
*/


CREATE FUNCTION [dbo].[JobCategoryStatusList]
(@JobCategory nvarchar(50)
, @HoursToCheck int = 24
, @IsSuccessful bit = 0
)
RETURNS @tAllJobsInStatus TABLE
(
JobName nvarchar(128)
, RunStatus bit
, LastTimeJobRan datetime
)
AS
BEGIN
--DECLARE @JobCatagory nvarchar(50) = 'SAP_to_staging';
--DECLARE @HoursToCheck int = 24;
--DECLARE @IsSuccessful bit = 1;
INSERT INTO @tAllJobsInStatus
SELECT
SJ.NAME AS [Job Name]
,RUN_STATUS AS [Run Status]
,MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) AS [Last Time Job Ran On]
FROM dbo.SYSJOBS SJ
LEFT OUTER JOIN dbo.SYSJOBHISTORY JH
ON SJ.job_id = JH.job_id
WHERE JH.step_id = 0
AND jh.run_status = @IsSuccessful
and category_id IN (select category_id
from dbo.syscategories
where name = @JobCategory)
and DATEDIFF(HH , DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME), getdate()) <= @HoursToCheck
GROUP BY SJ.name, JH.run_status
RETURN
END;