Allowing specific non-sysadmin users to query group membership for a login

I had a lot of good feedback about my post the other day about how to query group membership for a given login.

One tricky question was about how you could let a specfic user be able to find the group membership for another login, without the user being a sysadmin to run the code. Doing that is a bit trickier but can be done by creating a certificate, a login from the certificate, then assigning permissions to that login, and finally applying a digital signature to the procedure using the certificate.

Here’s a walkthrough:

Let’s start by creating a certificate that we’ll use for this purpose:

Next, we create a login from that certificate. Note that this isn’t a login that can actually log in, but is used as a container for the required permissions:

We need to then add that special new login to the sysadmin role so they can IMPERSONATE any login:

 

 

Next we create the stored procedure using the same techniques as I mentioned last time. It would be much simpler if we could just use WITH EXECUTE AS in the procedure to temporarily become an administrator but that doesn’t work.

We then digitally sign the stored procedure using the certificate. This will give anyone that runs the stored procedure sysadmin permission but only while they are running the procedure, and only if the procedure has not been modified in any way. Any change to the procedure will cause the digital signature to be dropped:

Just to check that the procedure works ok, I’ll run it as myself first:

 

That’s all good so let’s now work out if another user can run it:

 

Looks good to me so let’s clean up:

 

Hope that helps someone!