Audit startup procedures in SQL Server

Here is a script which will list all enabled or disabled startup procedures in SQL Server.

use [master]
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
DECLARE @procedureID INT
CREATE TABLE #procedureList(objectname varchar(100),startup_status varchar(50))
DECLARE procedure_cursor CURSOR FOR SELECT id
FROM master.dbo.sysobjects WHERE TYPE IN ('P','X')
OPEN procedure_cursor
FETCH NEXT FROM procedure_cursor INTO @procedureID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #procedureList
SELECT OBJECT_NAME(@procedureID)
,CASE Objectproperty(@procedureID,'ExecIsStartup')
WHEN 1
then 'Enabled'
else 'Disabled'
end
FETCH NEXT FROM procedure_cursor INTO @procedureID
END
CLOSE procedure_cursor
DEALLOCATE procedure_cursor
SELECT * FROM #procedureList WHERE startup_status = 'Enabled'
DROP TABLE #procedureList

 

Comments are closed.