Create abstraction procedures for SQL Server Agent jobs

Create_Agent_proxy_procedures.sql

USE msdb;
GO
CREATE USER Agent_job_abstraction
    WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo;
GO
ALTER ROLE SQLAgentOperatorRole
    ADD MEMBER Agent_job_abstraction;
GO
CREATE OR ALTER PROCEDURE dbo.Create_Agent_proxy_procedures
    @Principal_name     sysname=N'Agent_job_abstraction',
    @Category_schemas   bit=1,
    @Default_schema     sysname=N'Jobs'
AS





DECLARE @sql nvarchar(max);



--- Create a database user without login or password, add this user to the SQL
--- Server Agent operator role:
-------------------------------------------------------------------------------

IF (NOT EXISTS (SELECT NULL FROM sys.database_principals WHERE [name]=@Principal_name)) BEGIN;
    SET @sql=N'
    CREATE USER '+QUOTENAME(@Principal_name)+N' WITHOUT LOGIN;
    
    ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER '+QUOTENAME(@Principal_name)+N';
';

    PRINT @sql;
    EXECUTE sys.sp_executesql @sql;
END;



--- If @Category_schemas=1, make sure we have one schema for each job category.
--- Do not include "uncategorized" in this list - those will go to the default
--- schema.
-------------------------------------------------------------------------------

IF (@Category_schemas=1) BEGIN;
    SET @sql=N'';

    DECLARE sch_cur CURSOR FAST_FORWARD FOR
        SELECT DISTINCT N'CREATE SCHEMA '+QUOTENAME(REPLACE(REPLACE([name], N'[', N''), N']', N''))+N';'
        FROM dbo.syscategories
        WHERE SCHEMA_ID(REPLACE(REPLACE([name], N'[', N''), N']', N'')) IS NULL
          AND category_id IN (SELECT category_id FROM dbo.sysjobs)
          AND [name] NOT IN (N'[Uncategorized (Local)]', N'[Uncategorized (Multi-Server)]', N'[Uncategorized]');
    OPEN sch_cur;

    FETCH NEXT FROM sch_cur INTO @sql;
    WHILE (@@FETCH_STATUS=0) BEGIN;
        EXECUTE sys.sp_executesql @sql;
        FETCH NEXT FROM sch_cur INTO @sql;
    END;

    CLOSE sch_cur;
    DEALLOCATE sch_cur;
END;




--- Create the default schema (for uncategorized jobs, or if we don't want to
--- use different schemas per category).
-------------------------------------------------------------------------------

IF (SCHEMA_ID(@Default_schema) IS NULL) BEGIN;
    SET @sql=N'CREATE SCHEMA '+QUOTENAME(@Default_schema)+N';';
    PRINT @sql;
    EXECUTE sys.sp_executesql @sql;
END;




--- Delete orphaned job procedures
-------------------------------------------------------------------------------

SELECT @sql=STRING_AGG(N'DROP PROCEDURE '+QUOTENAME(s.[name])+N'.'+QUOTENAME(p.[name])+N';', N'
')
FROM sys.sql_modules AS m
INNER JOIN sys.procedures AS p ON m.[object_id]=p.[object_id]
INNER JOIN sys.schemas AS s ON p.[schema_id]=s.[schema_id]
WHERE m.[definition] LIKE N'%--- This procedure is automatically generated and maintained%'+
                          N'%--- Job identifier: <0x'+REPLICATE(N'[0-9A-F]', 32)+N'>%'
  AND s.[name] IN (
        SELECT @Default_schema
        UNION ALL
        SELECT REPLACE(REPLACE([name], N'[', N''), N']', N'')
        FROM dbo.syscategories)
  AND SUBSTRING(m.[definition], CHARINDEX(N'--- Job identifier: <0x', m.[definition])+21, 34) NOT IN (
        SELECT CONVERT(nvarchar(200), CAST(job_id AS varbinary(64)), 1)
        FROM dbo.sysjobs);

PRINT @sql;
EXECUTE sys.sp_executesql @sql;





--- Create/update the job procedures
-------------------------------------------------------------------------------

DECLARE proc_cur CURSOR FAST_FORWARD FOR
    SELECT N'
--- This procedure is automatically generated and maintained - any modifications may be overwritten.
--- Used to start job "'+REPLACE(REPLACE(j.[name], NCHAR(10), N''), NCHAR(13), N'')+'".
--- Job identifier: <'+CONVERT(nvarchar(200), CAST(j.job_id AS varbinary(64)), 1)+N'>

CREATE OR ALTER PROCEDURE '+QUOTENAME(ISNULL(REPLACE(REPLACE(c.[name], N'[', N''), N']', N''), @Default_schema))+N'.'+QUOTENAME(j.[name])+N'
    @server_name        sysname=NULL,
    @step_name          sysname=NULL
WITH EXECUTE AS '+QUOTENAME(@Principal_name, N'''')+N'
AS

EXECUTE dbo.sp_start_job
    @job_id='+CONVERT(nvarchar(200), CAST(j.job_id AS varbinary(64)), 1)+N',
    @server_name=@server_name,
    @step_name=@step_name;

'
    FROM dbo.sysjobs AS j
    LEFT JOIN dbo.syscategories AS c ON j.category_id=c.category_id AND c.[name] NOT IN (N'[Uncategorized (Local)]', N'[Uncategorized (Multi-Server)]', N'[Uncategorized]');

OPEN proc_cur;
FETCH NEXT FROM proc_cur INTO @sql;
WHILE (@@FETCH_STATUS=0) BEGIN;

    PRINT @sql;
    EXECUTE sys.sp_executesql @sql;

    FETCH NEXT FROM proc_cur INTO @sql;
END;

CLOSE proc_cur;
DEALLOCATE proc_cur;

GO