June 10, 2011 at 1:08 am
Hi,
SQL Server 2005 databases are migrated to SQL Server 2008 R2, but I found execute permission issue.
I have created new SP and that is called from Java code. The user is having permission to connect to a database and execute the stored procedures. But for new SP, execute permission is denied. Permission is granted to user through this command : "GRANT EXECUTE ON SP_name TO User_name"
What can be the reason for not having execute permission on new SP?
Thanks in advance,
Puja
June 10, 2011 at 1:43 am
- What's the exact error message ?
- Can you post your (new) sproc ddl ?
- can you post the ddl of a working sproc (by the same calling application)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 10, 2011 at 3:49 am
Error message:
The EXECUTE permission was denied on the object 'getReportDetails', database 'reports', schema 'dbo'.
SP ddl:
CREATE Procedure [dbo].[getReportDetails]
(
@InstId as BigInt
)
AS
Begin
set transaction isolation level read uncommitted
Set Nocount On
DECLARE @Template_id INT
SELECT RI.Inst_id , RI.Instance_Name , RT.Template_id ,
RT.Template_Name , RI.Author_user_id , RI.Author_org_id , RI.Created_date
FROM Report_Instance RI
Where RI.Inst_id = @InstId
Select @EmailCount = count(Email_Delivery_id) from Email_Delivery ED
INNER JOIN Report_Instance_temp RI ON ED.Instance_id = RI.Inst_id
WHERE RI.Inst_id = @InstId
End
I don't have Java code that is calling SP. After granting execute permission on SP for the user, SP call was successful.
June 10, 2011 at 4:02 am
By default, every execute permission needs to be granted explicitely ( unless the calling user is member of dbowner group or sproc owner ).
Since MS implemented Schema ( sql2005 ) it is advised to actually create your own schema and nolonger create objects in dbo.
With this, one can grant execute permission at schema level, so every granted account automatically is able to call new sprocs.
When using the "With execute as ... " option for a sproc, a user event doesn't need permissions on the onderlying objects to be able to use the sproc, which facilitates an even better/easier security model.
Check books online topic "create procedure" for more info.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 10, 2011 at 5:35 am
ALZDBA, Thanks for help.
Following command worked:
GRANT EXECUTE ON SCHEMA::[dbo] TO user_name
-Puja
June 10, 2011 at 6:06 am
Puja Shah (6/10/2011)
ALZDBA, Thanks for help.Following command worked:
GRANT EXECUTE ON SCHEMA::[dbo] TO user_name
-Puja
DO NOT grant to schema DBO !
This may be opening doors that shouldn't be.
The dbo schema contains more that your user sprocs and is to be considered a system (read "internal") schema (with sql2005 and 2008 MS introduces the sys schema containing their new systems stuff)
If you still have objects in the dbo schema, I would advise you to keep on granting them individually or - if you can (and my preference) - migrate them to a new schema.
Keep in mind you'll still have to test your app(s) !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 10, 2011 at 6:20 am
Will this command do ?
GRANT EXECUTE TO User_name
June 10, 2011 at 6:42 am
No.
Just perform the grant as you used to
grant execute on dbo.sproc to youruser ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 10, 2011 at 7:36 am
Thanks ALZDBA for help !!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply