How to deal: "The EXECUTE permission was denied on the object ‘sp_executesql’, database ‘mssqlsystemresource’, schema ‘sys’." issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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

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.

Pingback from User links about "permission" on iLinkShare
Pingback from 2005 Buick Roadmaster Cadillac Fleetwood, Roadmaster Bike Pedals
Pingback from P20 Aftermarket Auto Parts Clutch Kit, P2000 Novels Sale
Pingback from Extended Warranty Volvo S80, Volvo S80 Headlight Replacement Mileage
Pingback from Sale Sign Gmc Typhoon, Typhoon Hid Xenon Bulbs
Pingback from Ip1500 Part S15 Jimmy Chevrolet, P15 P1500 Car Part 1967 Gnc
Pingback from Pb250 Factory Rotor, Pb250 Turbo – 207.myipgirl.com
Pingback from Rd200 Scene, Rd2000 Auto Buy – 342.tijuanareader.com
Pingback from 1988 – 2009 @ Bmw 750il Performance Horsepower, 750il Used Vehicle Classified – 240.ja3ra.com
Pingback from Dark Under Eye
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |