SQL Server Performance

How to deal: "The EXECUTE permission was denied on the object 'sp_executesql', database 'mssqlsystemresource', schema 'sys'." issue

Discussion in 'SQL Server Knowledge Sharing Network (SqlServer-qa' started by satya, Aug 11, 2008.

  1. satya Moderator

    One of the TRUSTWORTHY features within SQL Server 2005 will be like when you are running a stored procedure with a dynamic execution query (sp_executesql or EXECUTE), you may receive the following error message:
    Server Msg 229, Level 14, State 5, Line 1 'permission' permission denied on object 'object', database 'database', owner 'owner'.
    As a Developer or DBA you wouldn't get confusion when trying to execute the above piece of code 'sp_executesql' as it is specific there explicitly, how about when this returns within a stored procedure execution. It is obvious that few of the processes will be executdd under a user login which may have DBO permissions and such confusion can occur as all the tables are owned by dbo, and all stored procedures are owned by dbo.
    Dynamic SQL is easy to get the things around when you do not have the direct permission on the tables, but sp_executesql has a different scope from the stored procedure it is called from, and it doesn't inherit the permissions from the stored procedure. So the statement inside sp_executesql is run with the permissions of the user who called the stored procedure, and that user might not have permissions on the table. Erland Sommarskog (SQL Server MVP) has given an excellent indepth of information about Curse-Blessings_of_DynamicSQL which is a definitieve guide for the Developers & DBAs and also I recommend to visit Fun_and_Fumes_DynamicSQL-Injection issues blog post that might affect the security of your database system.
    Coming to the issue resoution either you have to remove all the permissions to EXECUTE sp_executesql on that SQL instance or grant SELECT permissions on the stored procedure. Due to the reason that this behavior occurs because a dynamic execution query (sp_executesql or EXECUTE) executes in a separate context from the main stored procedure; it executes in the security context of the user that executes the stored procedure and not in the security context of the owner of the stored procedure.
    If you have deployed ownership chains then you must take the above approach into account while you are performing the process to grant the necessary permission for each underlying object that is mentioned in the dynamic execution query.
  2. Anonymous New Member

    Pingback from User links about "permission" on iLinkShare
  3. Anonymous New Member

    Pingback from 2005 Buick Roadmaster Cadillac Fleetwood, Roadmaster Bike Pedals
  4. Anonymous New Member

    Pingback from P20 Aftermarket Auto Parts Clutch Kit, P2000 Novels Sale
  5. Anonymous New Member

    Pingback from Extended Warranty Volvo S80, Volvo S80 Headlight Replacement Mileage
  6. Anonymous New Member

    Pingback from Sale Sign Gmc Typhoon, Typhoon Hid Xenon Bulbs
  7. Anonymous New Member

    Pingback from Ip1500 Part S15 Jimmy Chevrolet, P15 P1500 Car Part 1967 Gnc
  8. Anonymous New Member

    Pingback from Pb250 Factory Rotor, Pb250 Turbo - 207.myipgirl.com
  9. Anonymous New Member

    Pingback from Rd200 Scene, Rd2000 Auto Buy - 342.tijuanareader.com
  10. Anonymous New Member

    Pingback from 1988 - 2009 @ Bmw 750il Performance Horsepower, 750il Used Vehicle Classified - 240.ja3ra.com
  11. Anonymous New Member

    Pingback from Dark Under Eye

Share This Page