Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

When to Use Dynamic SQL Expand / Collapse
Author
Message
Posted Sunday, February 10, 2002 12:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/whendynamicsqlisuseful.asp



Robert W. Marda
SQL Programmer
Ipreo
Post #2568
Posted Monday, February 11, 2002 5:43 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 9:01 AM
Points: 6,705, Visits: 1,680
Hey Robert,

Good to see another article from you! Thanks for submitting. As far as dynamic sql, I've never worried about the performance (solve the problem, then seek performance if its an issue) rather the weakness in the security model that forces you to grant table access to the user of exec. I know Steve likes to use a solution where is has one proc per possible query but I think at some point that doesn't scale. A good example would be a search form with 9 or 10 fields - thats a lot of possibilities! Any ideas?








Andy


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #27916
Posted Monday, February 11, 2002 6:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
mbova:

I think the major thing about security is this:

Normally you can give execute permissions to a user for a stored procedure and the stored procedure will run fine.

However, with dynamic SQL in a stored procedure the permissions do not carry over to the code that is dynamic and so if the user has execute permissions to the stored procedure but not to a table in the dynamic SQL then they will get a permissions failure error.

In our database all users by default have read permissions so for us this is not an issue.

Robert Marda




Robert W. Marda
SQL Programmer
Ipreo
Post #27917
Posted Monday, February 11, 2002 6:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
Sorry for so many posts that are the same. I kept getting an error stating there was a problem and thought the post had not gone through.

Robert Marda




Robert W. Marda
SQL Programmer
Ipreo
Post #27918
Posted Monday, February 11, 2002 6:59 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: 2 days ago @ 3:54 PM
Points: 8,369, Visits: 733
I have used both static and dynamic queries in Stored Procedures for a long time now. I tend to try to avoid dynamic since I found out tht was the reason why I had to grant read access to tables so often. But I do agree with your statment:
quote:
I use dynamic SQL whenever it seems to be the best way to get the results I need and I firmly believe there is a place for dynamic SQL in almost every database
Good article on the subject.




Post #27919
Posted Monday, February 11, 2002 7:04 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
Andy:

We have some stored procedures that have 25 to 27 different fields a user can modify. These directly affect how the stored procedure limit the result set. About 6 months ago we converted one of our dynamic SQL stored procedures (the one that handles 27 different fields) from dynamic SQL to static. Now, instead of having one stored procedure we have 23 stored procedures. Many of them still have some dynamic SQL in them otherwise they would have been slower than the original. For the most part we have gained in performance and speed at a cost of extra maintenance.

Robert Marda




Robert W. Marda
SQL Programmer
Ipreo
Post #27920
Posted Monday, February 11, 2002 7:23 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: 2 days ago @ 3:54 PM
Points: 8,369, Visits: 733
In regards to what Robert said.
quote:
In our database all users by default have read permissions so for us this is not an issue.
I prefer to deal with this by creating roles to handle. First I hae a role called urProcExec which is the role for people with execute rights on the procedures and then I create a role called urReadTbl and put urProcExec in it giving rights on all tables to be read. This makes it easier for me to keep up with permissions and I can prevent user access better on tables that need not have any permissions in regards to procedures with dynamic SQL.




Post #27921
Posted Monday, February 11, 2002 8:29 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, April 03, 2014 10:06 PM
Points: 6,621, Visits: 1,851
mbova, here's the deal on security.

If we build stored procedures that contain only static SQL statements, we can take advantage of ownership chains. For instance, dbo owns the stored procedure and dbo also owns the tables and views referenced by the stored procedure. Because all the objects have the same owner, SQL Server will only check security when the stored procedure is executed. It makes the assumption that since the owner of the stored procedure also owns the objects referenced, the owner intended the person with execute rights on the stored procedure to have the appropriate rights to carry out the SQL statements within the context of the stored procedure. So if I don't normally have DELETE rights on a table, but a stored procedure I have the right to execute does a DELETE on the table, SQL Server will allow the DELETE through the stored procedure. If however, I were to try and execute a DELETE separate from the stored procedure, SQL Server will balk and prevent me from doing so. As a result, we can strictly control how data is accessed and modified. We simply give execute rights on the stored procedures and do not grant any rights on the tables and views.

When dynamic SQL enters the picture, however, things change. Even if I choose to execute a dynamic SQL statement from within a stored procedure, SQL Server will execute that dynamic SQL statement in a new context. It will be executed outside the context of the stored procedure, basically as an ad hoc query. SQL Server will check security with respect to the dynamic SQL statement, something we avoided in the static SQL inside a stored procedure example. Ownership chains become irrelevant with respect to the dynamic SQL statement.

That means if the DELETE is contained in a dynamic SQL statement and I don't have rights to DELETE from the table, SQL Server will balk. So what has to happen is where previously a user did not have to be granted DELETE permissions on the table, he or she now does. That means that the user can access and modify the data outside a stored procedure, in any manner rights allow.

K. Brian Kelley
bkelley@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bkelley/


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #27922
Posted Tuesday, February 12, 2002 12:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:31 PM
Points: 32,780, Visits: 14,941
Nice article with good examples. Personally, though, I have a rule of thumb: NEVER use dynamic SQL. That being said, I have used it in places, but only when I have tried every thing I can think of. Same as with cursors, you may need to use dynamic sql, but probably not. I think it should be your last resort, meaning you HAVE tried other solutions.

I also implement multiple stored procedures because the maintenance cost is less than the performance cost.

Regarding the article, I agree that the opening could be beefed up to present some refereces or reasons not to use dynamic sql. Lots of newbies will skip the short intro and start cutting and pasting code. Otherwise it was informative.

Steve Jones
steve@dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #27923
Posted Saturday, February 16, 2002 6:05 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
I guess part of the reason I am not against dynamic SQL is that when I arrived at bigdough.com in May 2000 almost all stored procedures were dynamic SQL. I have seen dynamic SQL in action a lot and it works very well for us. I am seeing that there are benefits to using static SQL, however it takes many days just to convert one stored procedure from dynamic SQL to static and then more time to test it.

I am gradually coming to the conclusion that static is generally better as long as you have the personel to make mass modifications when new functionality must be added. All our major stored procedures are modified about every three months. Since there are only two of us that modify stored procedures it is still easier to make changes in 200 hundred or so stored procedures instead of 5 or 600 hundred stored procedures.

Robert Marda




Robert W. Marda
SQL Programmer
Ipreo
Post #27924
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse