January 24, 2012 at 2:35 pm
Hello,
A colleague of mine is seeing the following type of error when a test web app is trying to execute a stored procedure in one of our databases:
The EXECUTE permission was denied on the object 'usp_update_table', database 'db1', schema 'dbo'.
I went to this page and used a couple of the queries listed to get db role and permission info. Results are listed following the link.
principal_nameprincipal_type_descclass_descobject_namepermission_namepermission_state_desc
db1_testSQL_USERDATABASENULLCONNECTGRANT
db1_testSQL_USEROBJECT_OR_COLUMNusp_update_tableCONTROLGRANT
db1_testSQL_USEROBJECT_OR_COLUMNusp_update_tableEXECUTEGRANT
db1_testSQL_USEROBJECT_OR_COLUMNusp_update_tableVIEW DEFINITIONGRANT
principal_nameprincipal_type_descclass_descobject_namepermission_namepermission_state_descrole_name
db1_testSQL_USERNULLNULLNULLNULLdb_datareader
db1_testSQL_USERNULLNULLNULLNULLdb_datawriter
db1_testSQL_USERDATABASENULLCONNECTGRANTNULL
db1_testSQL_USERDATABASENULLEXECUTEGRANTdb_executor
db1_testSQL_USEROBJECT_OR_COLUMNusp_update_tableCONTROLGRANTNULL
db1_testSQL_USEROBJECT_OR_COLUMNusp_update_tableEXECUTEGRANTNULL
db1_testSQL_USEROBJECT_OR_COLUMNusp_update_tableVIEW DEFINITIONGRANTNULL
Could anyone advise me on how I can find out why this error persists even though we have tried granting the EXECUTE permission to the db1_test SQL login via the SSMS GUI and T-SQL? I can't figure out why the granted permissions aren't working. One other important note is that I used this link at some point to try to set up a "db_executor" role for the database that would grant EXECUTE permission to logins simply by adding them to the role -
http://www.sqldbatips.com/showarticle.asp?ID=8
- and I wonder whether somehow that added role is interfering with my granting of permissions.
Thanks for any help. Let me know if any further information would be helpful.
Sincerely,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 24, 2012 at 2:43 pm
A couple of things to check. First, find out if your testuser is associated to any other roles. Then, double check there isn't an explicit deny on this proc. Permissions are additive (IE: If one role can execute one proc and another role can execute another and I have both roles associated, I can execute either), except when explicitly denied.
Otherwise, I'd doublecheck that you're logging in as the right user. It sounds like you've tested via SSMS and directly logged in as this user and are getting the same error from that. The permissions you pulled look correct, so I'm not sure what exactly could be stopping you except an explict deny on public or something like that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2012 at 3:30 pm
Evil Kraig F (1/24/2012)
A couple of things to check. First, find out if your testuser is associated to any other roles. Then, double check there isn't an explicit deny on this proc. Permissions are additive (IE: If one role can execute one proc and another role can execute another and I have both roles associated, I can execute either), except when explicitly denied.Otherwise, I'd doublecheck that you're logging in as the right user. It sounds like you've tested via SSMS and directly logged in as this user and are getting the same error from that. The permissions you pulled look correct, so I'm not sure what exactly could be stopping you except an explict deny on public or something like that.
Great, thanks so much for your reply. I'll look into the explicit deny possibility.
Sincerely,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply