 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / February 2008SP_EXECUTESQL Security in 2005
Hi, Recently moved a few DBs from '00 to '05. At that time our DBA changed the security to all SP's so that the impersonating user only has access to Execute the SP's. This seemed all fine and dandy, except that some of our SP's had dynamic SQL using SP_EXECUTESQL, which fail on security when executed. She would like me to change all the SP's to not be dynamic (which, in these cases, would not make sense). I understand what she is trying to do, but it seems silly, to me, to not be able to use SP_EXECUTESQL when "necessary". Is there a way, security wise (or something else outside the range of my question) to meet in the middle or is there a way to change my SP to be able to accomplish the same functionality? Thank you t I don't know exactly what the conditions were that made it fail but have a look at this: http://www.sommarskog.se/grantperm.html  SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Hi, > [quoted text clipped - 20 lines] > Thank you > t Thank you for responding Andrew. Thank you for the link ... I had read that link before and it was helpful. However, I believe I already understand why it is failing (my take): Since the Impersonating User only has access to run a SP, and since SP_EXECUTESQL is really building/creating and running a separate SP it does not have access to run it. (The error is receives is that is does not have access to the Object "Whatever Table"), This makes sense to me. My question really is more of a: Since the DBA only wants to grant access to the Impersonating User to execute the SP (which is understandable) and since I want to (in certain occasions) create Dynamic SQL (SP_EXECUTESQL) .... how can we make both situations work where we're both happy? Thank you t > I don't know exactly what the conditions were that made it fail but have a > look at this: [quoted text clipped - 24 lines] > > Thank you > > t > Thank you for the link ... I had read that link before and it was helpful. > [quoted text clipped - 10 lines] > occasions) create Dynamic SQL (SP_EXECUTESQL) .... how can we make both > situations work where we're both happy? First, I don't really know what your impersonating user comes from, but I suppose that is specific to your setup. Maybe you could give some more details, in case this could be relevant for the issue? The solution I recommend in my article is to sign the procedures with a certificate. Any reason this would not work for you? Of course, you would still need to convince your DBA that this is a sound way to go. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi, Your DBA changed the security rules so that a user can only execute stored procedures and no longer have rights to the tables and views. We are also developing our websites and applications with this rule. So we also bumped into your problem. We've been helped by the content in the links of Erland Sommarskog (thx). Basicaly what you have to do is : - create a certificate in the database - create a user from that certificate - give that user the necessary rights on your tables/views that are necessary to execute your sp_executesql statement - sign your stored procedure with that certificate ( add signature to ...) > > Thank you for the link ... I had read that link before and it was helpful. > > [quoted text clipped - 18 lines] > certificate. Any reason this would not work for you? Of course, you would > still need to convince your DBA that this is a sound way to go. Thank you for responding Andrew. Thank you for the link ... I had read that link before and it was helpful. However, I believe I already understand why it is failing (my take): Since the Impersonating User only has access to run a SP, and since SP_EXECUTESQL is really building/creating and running a separate SP it does not have access to run it. (The error is receives is that is does not have access to the Object "Whatever Table"), This makes sense to me. My question really is more of a: Since the DBA only wants to grant access to the Impersonating User to execute the SP (which is understandable) and since I want to (in certain occasions) create Dynamic SQL (SP_EXECUTESQL) .... how can we make both situations work where we're both happy? Thank you t > I don't know exactly what the conditions were that made it fail but have a > look at this: [quoted text clipped - 24 lines] > > Thank you > > t |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.