Give execute permission to user which does not have execute permission for specific stored procedure

Question

I have a user which just have view rights i.e. it does not have execute permission. But for a specific stored procedures I want it to have execute permission.So I followed the following process. A user have permission to execute stored procedure and B user does not have the execute permission.

So while creating on stored procedure I wrote as.

CREATE PROCEDURE sampleSP
WITH EXECUTE AS 'A'
AS
BEGIN
      --stored procedure body
END

And I am executing stored procedure as following with the login rights of B user.

EXEC sampleSP

But it is giving me the following error.

The EXECUTE permission was denied on the object 'sampleSP', database 'SAMPLEDB', schema 'dbo'.

Answer 1

While a procedure runs, normally it has access to data in the database like the owner of the procedure, which is usually "dbo" and can access everything. I may have got this wrong: I seem to be describing "EXECUTE AS OWNER" and I have documentation which says that the normal setting is "EXECUTE AS CALLER". I just thought that is what a "procedure" was -for-. Anyway, either is an option.

A procedure's references to objects in different databases use the rights of the actual user - they must do because a procedure only understands security in its own database. An exception is where a user's connection invokes an "application role"; in that case, the user has the access of the "guest" account to objects in other databases. This normally is or ought to be "no access".

None of this relates to who is allowed to execute a procedure in the first place, and that is what you're trying to set. That has to be allowed with GRANT.

However, if you issue GRANT and DENY commands that apply to the same object access, the result is to DENY - that's absolute. Perhaps you've made that mistake. To un-grant privileges, use REVOKE when you don't want to DENY. Also, normal practice is to GRANT or DENY privileges to roles, then manage which roles a user is a member of. Or even roles with privileges, whose members are roles with business functions (job titles), whose members are actual user accounts.

What "EXECUTE AS" does is to control the object access rights that a user may be "given" (or denied) in a procedure which the user doesn't have in an ordinary batch of Transact-SQL commands.

Answer 2

Adding WITH EXECUTE AS 'A' will cause whichever user executes the procedure to impersonate 'A' for the duration of the execution.

And I am executing stored procedure as following with the login rights of B user.

EXEC sampleSP

But it is giving me the following error.

The EXECUTE permission was denied on the object 'sampleSP', database 'SAMPLEDB', schema 'dbo'.

If you want User "B" to be able to execute the procedure, you need to grant execute to that user:

GRANT EXECUTE ON [dbo].[sampleSP] to [B]