The EXECUTE permission was denied on the object for user in db_owner

• webrunner

  SSC-Dedicated

  Points: 31991

  More actions

  September 12, 2019 at 2:54 pm

  Go to Answer

  #3679539

  Hello experts,

  A user who is in the db_owner role for a database reported that he is getting this error:

  Msg 229, Level 14, State 5, Procedure ReportProc, Line 1 [Batch Start Line 1]

  The EXECUTE permission was denied on the object...

  Does anyone know why EXECUTE would fail for an owner of the database? I assume it is my ignorance and that db_owner does not automatically get EXECUTE permissions on all stored procedures, but I am not sure.

  Thanks for any help,

  webrunner

   

  • This topic was modified 5 years, 9 months ago by 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

• ZZartin

  SSC-Dedicated

  Points: 30921

  More actions

  September 12, 2019 at 3:08 pm

  #3679547

  Well does that user have an explicit deny execute permissions?

• webrunner

  SSC-Dedicated

  Points: 31991

  More actions

  September 12, 2019 at 3:14 pm

  #3679552

  Thanks, ZZartin,

  I don't see any DENY entries for this database user. I used this query from another SSC forum post (replacing 'someUser' of course):

  SELECT *
  
  FROM sys.database_permissions
  
  WHERE grantee_principal_id = USER_ID('someUser');
  
  AND state_desc = 'DENY';
  
  https://www.sqlservercentral.com/forums/topic/list-denied-permissions-and-undo-a-deny

   

  Thanks again,

  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

• webrunner

  SSC-Dedicated

  Points: 31991

  More actions

  September 12, 2019 at 4:17 pm

  #3679592

  PS The user is now also reporting that he can't view object definitions or find/select from a table.

  I can see the SQL login in the db_owner group, so I have no idea what is happening here. Could it be a an issue with the SIDs (even though the mapping and checkbox are there in the GUI)?

  Thanks for any help,

  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

• webrunner

  SSC-Dedicated

  Points: 31991

  More actions

  September 12, 2019 at 4:29 pm

  Answer

  #3679594

  PS I think this was an issue where the user name was a SQL login but also had a corresponding Windows login, so may be just confusion between the two. Will let you know.

  EDIT: Yes, it was a login confusion issue. Which is a relief, because it was starting to drive me crazy.

  • This reply was modified 5 years, 9 months ago by 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