SQL: Use CREATE OR ALTER to deploy procedures

In the last few weeks, I’ve seen a surprising number of database scripts that deploy procedures and functions by dropping the object if it exists, then recreating it. Please don’t write scripts like this.

When you drop a procedure, you not only drop the procedure, but you drop all permissions associated with it. If you then create the procedure again, you’ll have the procedure, but you won’t have the permissions.

That’s the fundamental reason why ALTER PROCEDURE is useful. It changes the procedure without affecting permissions.

CREATE OR ALTER

Because there wasn’t a clean way to create a procedure (prior to SQL Server 2016 Service Pack 1) if it wasn’t already there, and to just alter it if it was, CREATE OR ALTER PROCEDURE was introduced.

This should now be the basis of your deployment code. It creates the procedure if it’s not already there, but if it is already there, it alters it, and this keeps the permissions.

I now almost never write either CREATE PROCEDURE or ALTER PROCEDURE. I always just write CREATE OR ALTER PROCEDURE.

Digitally Signed Procedures

Note that this only applies to normal SQL permissions applied to the procedure. If you have permissions assigned indirectly through digital signatures on the procedure, they’ll be gone when ALTER PROCEDURE is used, and that’s as expected. If you change the procedure, you lose the signature, and that’s the desired outcome.

2021-03-11