SQL Server Performance

Execute Permission Denied!

Discussion in 'General Developer Questions' started by Bredsox, Aug 10, 2005.

  1. Bredsox New Member

    Hi,
    When I tried to run a stored procedure as dbo (not in Master), it is giving me this error:

    New custid created: 1008915
    Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 85
    EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
    Server: Msg 229, Level 14, State 1, Procedure sp_OAMethod, Line 109
    EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'.
    Server: Msg 229, Level 14, State 1, Procedure sp_OADestroy, Line 141
    EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'.

    Does anyone have idea what could be the reason? FYI... My Stored Procedure calls other stored procedures to execute this create customer task but dbo should have all the previledges, right? When I checked in other server, it says GRANTEE as Public but the server that I am working on says: GRANTEE as db_execsps. Thanks everyone for your time.

    Dan
  2. Twan New Member

    In SQL 2000 SP3 a dbo in one database is not automatically a dbo in other database. The user who is executing the stored proc will need permission on the procs in the master database.

    Alternatively you can go back to pre-sp3 settings by turning of database chaining

    Cheers
    Twan
  3. dineshasanka Moderator

    BOL states
    When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure. However, if a user owns two stored procedures in which the first procedure calls the second, then EXECUTE permission checking is not performed for the second stored procedure.

    http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=45799
    http://www.sqlservercentral.com/forums/messages.aspx?forumid=8&messageid=9644
  4. Twan New Member

    There should be an exception added to the end of that which is except when the two procedures (which are owned by the same user) are in different databases and Allow cross-database ownership chaining is turned off (introduced in SP3)

    Cheers
    Twan
  5. Bredsox New Member

    Thanks everyone for your reply. Appreciated.
    Dan

Share This Page