Permissions not granted on spName...
-
Here's the situation. User is in role A. Role A is granted EXEC on the dbo schema. So, my understanding is that any user, barring an explicit deny/revoke on a procedure, that is in the A role has permissions to execute any procedure in the dbo schema. Yet, on one procedure we are getting "Pemissions not granted on spName for ". Any ideas what to look at to troubleshoot?
I have verified that the procedure is only accessing objects in the dbo schema so ownership chaining applies. I have run the procedure successfully in SSMS using EXECUTE AS . There is no explicit deny.
Just looking for some tips as to where else to look?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I would have guessed that the user is in another role that deny the execution of that sproc...
Did you check the effective permission for that sproc?
EXECUTE AS USER = 'User';SELECT * FROM fn_my_permissions('dbo.spName', 'OBJECT')
ORDER BY subentity_name, permission_name ;
REVERT;
-
What is the actual error message? And where do you get it? The message you quote is far from the regular error message:
The EXECUTE permission was denied on the object 'some_sp', database 'sommar', schema 'dbo'.
So this sounds like an error from an application - in which case may not be a permissions problem, but a case of an application jumping to conclusion. Time for some tracing?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
-
LutzM (12/23/2014)
I would have guessed that the user is in another role that deny the execution of that sproc...Did you check the effective permission for that sproc?
EXECUTE AS USER = 'User';SELECT * FROM fn_my_permissions('dbo.spName', 'OBJECT')
ORDER BY subentity_name, permission_name ;
REVERT;
So, I just tried this and, because the permissions are granted to a role this function doesn't return anything for the user, but thanks because I hadn't thought of using this function.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Erland,
Thanks. That's where I was thinking of going as well. Just hoping I could find some way to do do it without going to trace or xe.
Turns out I don't have to anyway, because as soon as they ran the app with me watching it worked. I didn't make any changes to permissions in the database, so there is/was something funky with the application.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Even though it looks like an issue at the app layer, here's a link to a question over at stackoverflow including a query that'll list all user permissions including those based on roles.
-
@Jack... was it using an AD acct? Cause I've seen that before where the windows layer flakes out from time to time and isn't able to authenticate properly. Though if it was just happening for that one object that's a little weird and I would have a harder time believing that.
Also I would try giving it explicit exec perms to see if that cleared it up. You know, just as a tshooting step.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply