SQL: Use elevated procedure permissions instead of elevated user permissions

SQL: Use elevated procedure permissions instead of elevated user permissions

Choosing the right database permission can be hard. I’ve lost count of the number of times I’ve heard a discussion like this:

I need to let Mary restore truncate one of the tables but I don’t want to give her permission to do it, in case she stuffs it up.

or

I need to let Paul restore this database but I don’t want him to be able to restore other databases, and I’m worried if I give him the permission, he might accidentally do something bad and I’ll be blamed for it.

Whenever you have this type of discussion, the problem is that you’re looking to give the user a permission, but only in a very limited situation and the DCL (data control language) statements (ie: GRANT, DENY, REVOKE) are too coarse for what you’re trying to do.

Instead, what you need to do is to create a stored procedure, to give the stored procedure the permission to do what’s needed, and then just give the user permission to execute the stored procedure.

There are two basic ways to do this.

The first is to create the stored procedure with a WITH EXECUTE AS clause. For example, I if write this:

CREATE PROCEDURE Utility.DoSomethingPotentiallyScary WITH EXECUTE AS OWNER AS …

then whatever the procedure does is executed as the owner, not as the user. And this includes any dynamic SQL code. It’s documented here. For stored procedures, instead of OWNER, you can also have CALLER (that’s the default anyway), SELF (ie: the person creating the procedure), or a specific user.

To create or alter a procedure to execute as someone else, you need to have IMPERSONATE permission on that user. (That’s already there if you’re an admin).

That’s a pretty simple solution but it has a few limitations.

For trickier scenarios (such as some cross-database scenarios), you can do this instead:

  • Create a certificate
  • Create a user from that certificate
  • Add the required permissions to that special user
  • Digitally sign the stored procedure with the certificate

Now when the stored procedure runs, it will acquire the permissions associated with that certificate, but only while it runs. An added bonus is that if the stored procedure is changed in any way, the digital signature is removed, along with the permissions.

2018-08-06