question

jhard avatar image
jhard asked

db owner unable to access stored procedures

Using SSMS 2016 sa user can't see stored procedures in a database. (can see all sys stored procedures) I am logged in as db owner; I can see them, but I can't execute them. I get this message: > Msg 2812, Level 16, State 62, Line 1 > Could not find stored procedure > 'GetConvRows'. If I right-click on the procedure in the object explorer, I can execute it.
executestored procedure
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ange avatar image
Ange answered
***SEE COMMENTS BELOW*** You may need to give yourself permissions to execute stored procedures. You will have to create the role first though. CREATE ROLE sp_execute GRANT EXECUTE TO sp_execute Then grant yourself the sp_execute role for that particular database.
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I believe this should not have been marked as the accepted answer as it could lead the future users down the wrong path. At least an edit to the answer is required as the users might not read the comments carefully. The actual problem seems to be no mentioning of the schema name while executing the procedure.
1 Like 1 ·
Thank You!. very direct to the point. That of course worked. I had assumed my ownership of the database would make it so, but after studying up on it. I see why now.
0 Likes 0 ·
Just to clarify, if you are truly in the DBO role, you do not need to create a new role nor grant explicit execute permissions. The DBO facilitates you to do just about anything to the database, including dropping it from the instance. Anybody in the sysadmin server role would have carte blanche permissions to any database on the instance. That why is should be under lock and key. I would not create a specific role for this rather determine what the cause of the error is. Creating another role just makes things more convoluted in my opinion. If the procedure is doing a cross database join, however, in which you do not have permissions, that could cause a problem.
3 Likes 3 ·
That's very possible as it would default to the dbo schema. That would also explain why the OP could right click on the procedure and execute it.
3 Likes 3 ·
Glad to be of help. Please don't forget to mark the answer as complete and a thumbs up appreciated :-)
0 Likes 0 ·
Show more comments
schema on the procedure is dbo
0 Likes 0 ·
Is it possible your default schema is different? Are you putting it as exec dbo.GetConvRows
0 Likes 0 ·
jhard avatar image
jhard answered
can either of you recommend a good source for gaining a full understanding of rights and priviledges in the new SQL Server environment? My experience is way too old. Sybase in the 1990's
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think that I'd start here: http://www.sqlservercentral.com/stairway/110890/. It should be a good stairway series on SQL Server security and will get you started.
0 Likes 0 ·
Thanks John M. I checked it out. looks like a good start.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.