Cannot grant EXECUTE on my stored procedure
Hello! I wrote stored procedure 'SubmitPayment' and want to give rigth on execution to another user, called 'teller'.
Firstly, I did this through configuration manager.
When this didn't help, I made query:
use billing
grant execute on [dbo].[SubmitPayment] to teller
Anyway, when I then connect as 'teller' and try to execute
USE [Billing]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[SubmitPayment]
@PaymentId = 31
SELECT 'Return Value' = @return_value
GO
I get this error:
' 07@5H5=85 EXECUTE 70?@5I5=> 4;O >1J5:B0 "SubmitPayment", 107K 40==KE "Billing", AE5<K "dbo".'
Which is in English: 'EXECUTE permission is forbidden for object "SubmitPayment", database "Billing"'
Under 'dbo' it works fine, but I need this to work with 'teller' rights.
Firstly, I did this through configuration manager.
When this didn't help, I made query:
use billing
grant execute on [dbo].[SubmitPayment] to teller
Anyway, when I then connect as 'teller' and try to execute
USE [Billing]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[SubmitPayment]
@PaymentId = 31
SELECT 'Return Value' = @return_value
GO
I get this error:
' 07@5H5=85 EXECUTE 70?@5I5=> 4;O >1J5:B0 "SubmitPayment", 107K 40==KE "Billing", AE5<K "dbo".'
Which is in English: 'EXECUTE permission is forbidden for object "SubmitPayment", database "Billing"'
Under 'dbo' it works fine, but I need this to work with 'teller' rights.
ASKER
Yes, and this is the point, the fine distinction of rihts. After solving this problem I will have to learn how to use "with execute as 'dbo'" statement (and this is another problem, because SQL engine does not accept 'dbo' as a user in this context).
But first of all, I need to solve the problem stated above: give permission on procedure.
P.S. I use SQL Server 2005 and Studio Express
But first of all, I need to solve the problem stated above: give permission on procedure.
P.S. I use SQL Server 2005 and Studio Express
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
actually, you will be solve the dynamic sql permissions issue exactly with the "WITH EXECUTE AS ..." syntax.
please try:
WITH EXECUTE AS OWNER
please try:
WITH EXECUTE AS OWNER
ASKER
Well, I tried 'WITH EXECUTE AS OWNER', but at this stage this didn't help.
As I stated, the problem is that the user has no right to execute procedure.
To get clear experiment, I removed all commands from the procedure's body.
And still there's the same error (I have non-English version, so that it is my translation to English): 'EXECUTE permission is forbidden for object "SubmitPayment", database "Billing"'
The advice you have given me seems to be applicable to the next stage, when I have to deal with commands inside the procedure.
As I stated, the problem is that the user has no right to execute procedure.
To get clear experiment, I removed all commands from the procedure's body.
And still there's the same error (I have non-English version, so that it is my translation to English): 'EXECUTE permission is forbidden for object "SubmitPayment", database "Billing"'
The advice you have given me seems to be applicable to the next stage, when I have to deal with commands inside the procedure.
I see.
well, the only explanation I have then is that you are NOT connected as "teller".
well, the only explanation I have then is that you are NOT connected as "teller".
ASKER
To tell exactly how it is: There is a role "teller" in database "billing".
There is user "Leanne" in database "Billing" who belongs to role "teller".
There is login name "Leanne" in SQL server which corresponds to the role "teller".
I attached 3 screenshots. I am sorry before non-Russian readers, but, I hope, they would be clear, because main points are marked and in English.
"Login-user.jpg" shows correspondence between Login Name and Database User. Also on background one can see unsuccessfull attempt to execute "SubmitPayment" procedure, and in the status string below - login name "Leanne", under which the attempt had been made.
"User-Role.jpg" shows correspondence between user "Leanne" and role "teller".
"teller-rights.jpg" shows checks in front of "EXECUTE" for procedure "SubmitPayment" for role "teller".
Is something missing in rights assignment?
P.S. In the same way I gave "teller" permission on update, delete, insert for the tables 'Bills' and 'Payments' which worked fine - I tested it using connection under "Leanne". But unfortunately the same trick does not work with procedure :( although it seems to me that I configured everything according to books and manuals
There is user "Leanne" in database "Billing" who belongs to role "teller".
There is login name "Leanne" in SQL server which corresponds to the role "teller".
I attached 3 screenshots. I am sorry before non-Russian readers, but, I hope, they would be clear, because main points are marked and in English.
"Login-user.jpg" shows correspondence between Login Name and Database User. Also on background one can see unsuccessfull attempt to execute "SubmitPayment" procedure, and in the status string below - login name "Leanne", under which the attempt had been made.
"User-Role.jpg" shows correspondence between user "Leanne" and role "teller".
"teller-rights.jpg" shows checks in front of "EXECUTE" for procedure "SubmitPayment" for role "teller".
Is something missing in rights assignment?
P.S. In the same way I gave "teller" permission on update, delete, insert for the tables 'Bills' and 'Payments' which worked fine - I tested it using connection under "Leanne". But unfortunately the same trick does not work with procedure :( although it seems to me that I configured everything according to books and manuals
ASKER
yes, that looks all right, apart that the role "teller" shall not have the ALTER, CONTROL, or TAKE OWNERSHIP permissions, but that is another (security) issue.
so, what is the stored procedure code, right now?
so, what is the stored procedure code, right now?
ASKER
ALTER PROCEDURE [dbo].[SubmitPayment]
@PaymentId int,
@AdditionalInfo nvarchar(MAX),
@OrderId int,
@Referalnoye_voznagrajdeni
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
END
P.S.
Initially I granted "teller" only "execute" and "view definition". Other permissions were granted in a bid to make the "SubmitPayment" run :)
@PaymentId int,
@AdditionalInfo nvarchar(MAX),
@OrderId int,
@Referalnoye_voznagrajdeni
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
END
P.S.
Initially I granted "teller" only "execute" and "view definition". Other permissions were granted in a bid to make the "SubmitPayment" run :)
ASKER
Here's message in English which I got on another SQL server:
The EXECUTE permission was denied on the object 'SubmitPayment', database 'Billing', schema 'dbo'.
The EXECUTE permission was denied on the object 'SubmitPayment', database 'Billing', schema 'dbo'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, right you are!
Thank you, angelIII!
There was "deny" on "Execute" in database "Billing" for role "teller"
1) I have to be more careful in future.
2) I will install English version of SQL Server, because English diagnostics is clearer.
a) It is easier for others to help me
b) It is clearer to me. I must admit, that "was denied" hints on "deny" rule somewhere, whereas its Russian translation does not.
Thank you, angelIII!
There was "deny" on "Execute" in database "Billing" for role "teller"
1) I have to be more careful in future.
2) I will install English version of SQL Server, because English diagnostics is clearer.
a) It is easier for others to help me
b) It is clearer to me. I must admit, that "was denied" hints on "deny" rule somewhere, whereas its Russian translation does not.
ASKER
Thank you, angelIII!
There was "deny" on "Execute" in database "Billing" for role "teller"
1) I have to be more careful in future.
2) I will install English version of SQL Server, because English diagnostics is clearer.
a) It is easier for others to help me
b) It is clearer to me. I must admit, that "was denied" hints on "deny" rule somewhere, whereas its Russian translation does not.
There was "deny" on "Execute" in database "Billing" for role "teller"
1) I have to be more careful in future.
2) I will install English version of SQL Server, because English diagnostics is clearer.
a) It is easier for others to help me
b) It is clearer to me. I must admit, that "was denied" hints on "deny" rule somewhere, whereas its Russian translation does not.
An alternative is to have the stored procedure excute as another user so that when teller calls it, it actually runs as the other user.
-Rowan