Another audit picks up “Scan for Startup Procs” and flags it as a problem. The recommendation is to turn it off. I disagree, and here’s why…

Like my previous post about xp_cmdshell, I have a stake here. We use this stored procedure in our monitoring so whenever SQL starts we can check the error logs and calculate downtime etc. It’s useful, and this is exactly the scenario that I believe the configuration is made for.

Unlike xp_cmdshell though I can see a likely configuration where this can be a problem. So let’s talk about what it does.

If you enable this option SQL will scan for stored procedures marked to run on startup next time it restarts(As a side note you need to restart the server to enable it). You can mark a stored procedure to run on startup by using sp_procoption. And when I say you, I mean YOU, the sysadmin.

Yes the only user who has the ability to mark a stored procedure to run on startup is a sysadmin, so like xp_cmdshell this is a vulnerability that can only be exploited by a sysadmin. And if they are already a sysadmin they can turn it on anyway. Also…you know…they are a sysadmin. If they are malicious you have bigger problems than them running a stored procedure on startup. They can run ANY stored procedure, right now.

Of course, it’s a bit more complex than this. In this case the stored procedure is only required to mark a stored procedure to run on startup. It is possible for a user without sysadmin privileges who has the correct rights on the master database to alter the stored procedure. So yes, there is a vector here to elevate db owner on master to sysadmin, and then do whatever else you want.

This is where I have issues with some of these generic recommendations in audits. They are painted as black and white and as we all know the answer in SQL is always ‘It depends’. But when you just label something as ‘bad’ and people just accept that rather than ask why and how, perfectly safe and useful pieces of functionality are turned off.

A better approach would be to treat this finding as the start of a conversation, sit down with the server owners, understand if the functionality is needed and if so what mitigations can be put in place to ensure it is not a risk. There’s not many good reasons for giving a user elevated rights on the master database.

I’ll stop now because this post has a lot of potential to turn into a rant on the difference between compliance and security, but if you found this blog looking for a recommendation about using “Scan for Startup Procs” my advise would be this. If there’s another way of doing what you want that is equally efficient, use that. “Scan for Startup Procs” is a setting you will find yourself having to defend every time there is an audit of your server and you may not want to fight that fight. If you do want to fight it, making the setting safe is very simple. Don’t let anyone have rights to edit the stored procedure you create and you are fine. Bare in mind however that with all your good intentions, one day you may not be there, the guidance may not be followed and someone will be able to exploit this.