Creating Default Stored Procedures for New Databases in SQL Server

Does it have to execute immediately after the restore finishes, or can it be briefly delayed? One idea I had is to have a job that runs every minute, and checks for the Audit Backup/Restore Event in the default trace.

DECLARE @fn VARCHAR(MAX);

SELECT @fn = SUBSTRING([path], 0, LEN([path])-CHARINDEX(CHAR(92), REVERSE([path]))+1) 
  + CHAR(92) + 'Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT 
    DatabaseName,
    StartTime, 
    TextData
FROM sys.fn_trace_gettable(@fn, DEFAULT)  
WHERE EventClass = 115
AND TextData LIKE '%RESTORE%'; -- since can't differentiate between backup/restore

You could store the cleanups you've already done based on StartTime, and even limit the trace query that runs every minute (or whatever your acceptable delay threshold is) to only look at StartTime values greater than the last row you pulled, or the last time the job ran, whichever is less.

It is not a risk at all. In fact, the ability to turn ANSI_NULLS OFF has a limited lifespan. Soon enough that won't even be an option, it'll always be ON. The only time I've ever seen any form of reasoning for setting ANSI_NULLS OFF, is because there are developers that can't fathom the true meaning of NULL, and therefore want to use typical conditional logic with it. Again, terrible reasoning and not justifiable. I look forward to the permanent ON setting for ANSI_NULLS.