0

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'.

2

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.

0

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]
  • I did the same but still it didn't worked. – Sumit Dobhal May 30 '17 at 9:24
  • @SumitDobhal, did you also remove the EXECUTE AS clause from the stored procedure? It is not necessary as long as the ownership chain is unbroken. – Dan Guzman May 30 '17 at 11:24
  • @DanGuzman Yes I have checked still the same error. – Sumit Dobhal May 30 '17 at 12:03
  • The GRANT will work unless you have a DENY somewhere in the permission chain. Check for an explicit DENY to the user or a role the user belongs to and REVOKE the denied permission. – Dan Guzman May 30 '17 at 12:11
  • Yes the user does not have permission to execute. I am explicitly giving him permission using GRANT EXECUTE ON [dbo].[sampleSP] to [B] Is that a problem?? – Sumit Dobhal May 30 '17 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.