When a sysadmin runs a one-time use script, we have a folder we keep for all one-time use scripts that are run against our databases. This folder is not terribly searchable, making reuse of scripted code difficult. So, I was thinking, could we put all of these scripts into the database somehow? Here is what I was thinking:

We add all scripts as stored procedures. For one-time use scripts that we want to ensure will only ever be run once, we add a a DENY EXECUTE permission inside the procedure. We can store all of these scripts in an admin schema and set the permissions for that schema to sysadmin only. This way, if there are repeatable scripts, we don't need to keep track of where they are in the script folder. We can just re-execute a stored procedure. If we want to find a stored procedure that does something, we can add comments to the stored procedure and search for text matching what we are looking for.

What concerns (security or otherwise) might I want to address when considering something like this? Is this just too dangerous of an idea, and not worth considering? Or is this a fine idea, but a waste of time?

CodePudding user response：

Searching through SP's isn't terrible searchable either, I'm just not sure storing them in a database in this way is going to solve some of the issues you are experiencing. I also would be nervous that someone would forget deny execute and be able to run a script, on accident or on-purpose, which could lead to a big headache.

I prefer to keep these kinds of scripts in a code repository, such as git. Then you get all of the features, change tracking, easier branching, managing merges, etc. I used to store deployment scripts in source control with 0001-alter-tableX-add-columny or something like this, just to keep the scripts in order of execution (may not matter in your case).

If this is not possible, maybe I would prefer a document repository like Confluence over all as SP's. What if your database gets stuck in Restore/Recovery state, and you need to access one of these SP's, for example. What if things get corrupted? You may lose access or completely lose these scripts.