{"id":1217,"date":"2026-03-11T09:00:00","date_gmt":"2026-03-11T14:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=1217"},"modified":"2026-03-10T15:33:16","modified_gmt":"2026-03-10T20:33:16","slug":"sql-server-sp-ssis-startup-ssisdb-catalog","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=1217","title":{"rendered":"Startup stored procedures and SSIS: don’t disable sp_ssis_startup"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

If your SQL Server environment uses the SSIS Catalog (SSISDB), there’s one startup procedure you should leave alone: sp_ssis_startup<\/code>. It’s a frequent target of security hardening scripts and scanner recommendations, but disabling it creates real operational problems that are easy to overlook until something breaks at an inconvenient time. This post explains what it actually does, why security tools flag it, and why the right answer is almost always to leave it enabled.<\/p>\n\n\n\n

What the SSIS Catalog is<\/h3>\n\n\n\n

The SSIS Catalog (SSISDB) is the central repository for SSIS projects, packages, configurations, and execution history. It’s where you manage deployments, track execution status, and troubleshoot failures, both through SSMS and directly through the SSISDB catalog views and stored procedures.<\/p>\n\n\n\n

What sp_ssis_startup actually does<\/h3>\n\n\n\n

When enabled, SQL Server executes sp_ssis_startup<\/code> automatically at service startup. The procedure itself is minimal. It calls SSISDB.catalog.startup<\/code>, which performs one specific task: reconciling the state of any package executions that were active when the instance last went down. If SQL Server was restarted mid-execution, those runs need to be marked appropriately rather than left in a permanent “Running” state.<\/p>\n\n\n\n

That’s it. It’s a targeted cleanup routine, not a broad or privileged operation.<\/p>\n\n\n\n

Why security tools flag it<\/h3>\n\n\n\n

Security scanners flag sp_ssis_startup<\/code> because auto-executing startup procedures are a known persistence technique. In SQL Server, any procedure in master can be registered to run at startup via sp_procoption<\/code>, and that flag is visible in sys.procedures<\/code> as is_auto_executed = 1<\/code>. Scanners treat any entry in that list as a potential red flag by default, regardless of what the procedure actually does.<\/p>\n\n\n\n

The threat model here is worth understanding clearly. Registering or modifying a startup procedure requires sysadmin membership, and the procedure must live in master. If an attacker already has sysadmin, disabling sp_ssis_startup<\/code> is the least of your concerns. You have a full compromise. The startup mechanism itself is not the vulnerability; unrestricted sysadmin access is.<\/p>\n\n\n\n

What happens if you disable it<\/h3>\n\n\n\n

This is where the real-world impact shows up. If sp_ssis_startup<\/code> is disabled and the SQL Server service restarts, executions that were active at the time of shutdown will remain in status 2 (Running) rather than being corrected to status 6 (Ended Unexpectedly). That stale state ripples through in a few ways.<\/p>\n\n\n\n

The SSMS Active Operations and All Executions reports pull from SSISDB views, so your monitoring dashboards will show incorrect execution counts and statuses. Any job or runbook that checks for currently active executions before starting a new run may refuse to execute because it sees a ghost execution still marked as running. Troubleshooting becomes harder too. When catalog.executions<\/code> and catalog.operation_messages<\/code> are in an inconsistent state, root cause analysis takes longer and the history you’re relying on can’t be trusted.<\/p>\n\n\n\n

None of these failures are loud. They tend to surface quietly as scheduling conflicts or misleading reports, which makes them harder to connect back to the configuration change that caused them.<\/p>\n\n\n\n

Verifying your configuration<\/h3>\n\n\n\n

To check whether sp_ssis_startup<\/code> is currently registered for auto-execution:<\/p>\n\n\n\n

USE master;\nGO\nSELECT p.name,\n       p.is_auto_executed\nFROM sys.procedures AS p\nWHERE p.name = N'sp_ssis_startup';<\/code><\/pre>\n\n\n\n
To review SSISDB catalog properties including retention and cleanup settings:<\/p>\n\n\n\n
USE SSISDB;\nGO\nSELECT property_name, property_value\nFROM catalog.catalog_properties;<\/code><\/pre>\n\n\n\n
To inspect recent executions and their statuses:<\/p>\n\n\n\n
USE SSISDB;\nGO\nSELECT TOP 20\n       execution_id, folder_name, project_name, package_name,\n       status, start_time, end_time\nFROM catalog.executions\nORDER BY start_time DESC;<\/code><\/pre>\n\n\n\n
Recommended approach<\/h3>\n\n\n\n
Keep sp_ssis_startup<\/code> enabled if you’re using SSISDB. It is part of Microsoft’s intended catalog maintenance behavior and has been verified against the SQL Server 2019, 2022, and 2025 documentation. If a security review flags it, the right response is to document its purpose and scope, not to disable it. Limit and audit sysadmin membership separately. That’s where the actual exposure lives.<\/p>\n\n\n\n
References<\/h3>\n\n\n\n