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:

image

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:

image

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

 

image

 

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.

image

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:

image

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

 

image

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

image

 

Looks good to me so let's clean up:

 

image

Hope that helps someone!

2 thoughts on “Allowing specific non-sysadmin users to query group membership for a login”

  1. This is going to be extremely helpful. Thank you for the write-up.  
    Great example and easy to follow.  Well written.

Leave a Reply

Your email address will not be published. Required fields are marked *