There are couple ways to achieve what you are after.
I will show you two, but there are other ways as well.
The first one is signing a procedure, and allowing other users to use it with specifying
grant execute on object::yourprocedure only.
First of all you need a master key, which i assume you already have, and you can check it using
sys.symmetric_keys DMV, and looking for record that has '##' as a prefix. If not simply create a DB master key
'CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'P@$$W0RD'
This is very importaint key, and make sure you back it up using this guide
Now you are going to create a certificate based on which you will create module signing user. Note it is only for singing modules and you cannot use it to test privileges etc. So first things first - Certificate :
CREATE CERTIFICATE SignModules
WITH SUBJECT = 'Certificate for signing modules'
Just like the master key, you need to back up this certificate as well. guide
Now we are creating a user with certificate which will have all the rights:
CREATE USER UnlockCertifiedProcedures FOR CERTIFICATE SignModules
There are two ways of providing necessary rights:
- becoming a db_reader as
exec sp_addrolemember 'db_datareader',UnlockCertifiedProcedures
- giving appropriate rights
GRANT VIEW DEFINITION TO UnlockCertifiedProcedures,
GRANT VIEW DATABASE STATE TO UnlockCertifiedProcedures
Once this is done, the next step is to actually sign this procedure, so whoever has rights to 'execute' the procedure will inherit rights from the certificate user.
ADD SIGNATURE TO Schema.YourProcedure
BY CERTIFICATE SignModules
create user TestUser WITHOUT LOGIN
grant execute on OBJECT::YourProcedure to TestUser
execute as user = 'TestUser'
The second option is creating a user and executing procedure as that specified user.
CREATE USER AnotherTest WITHOUT LOGIN
GRANT VIEW DEFINITION TO AnotherTest
GRANT VIEW DATABASE STATE To AnotherTest
GRANT EXECUTE ON OBJECT::YourProcedure To AnotherTest
Create procedure executing as that user:
CREATE proc Schema.YourProcedure WITH EXECUTE AS 'AnotherTest'
Now simply give execute rights on a procedure to a specified user :
GRANT EXECUTE ON OBJECT::YourProcedure To SomeUser
Like i said there are other ways, including roles but these two could get you a job done. Just remember you cannot track a user that executed procedure by specifying 'execute as'(login is possible tho). Also creating a certificate/database keys can be a headache when you`re migrating DB, or simply restoring it somewhere else.