The Security Argument
One of the most frustrating things about watching the SP or not debate is how misused this particular argument is for the use of SPs. For instance, Rob says:
What permissions are required for working with your data? If embedded SQL is used it likely means that the application can execute any INSERT, UPDATE, DELETE, SELECT script it desires. You wouldn't – hopefully you don't – run as the administrator (sa) account on your server, so why allow full access to the database itself?
Some of what Rob says is true. Many people probably do give full access to all tables to the user who is accessing his or her database from his or her application. This is a terrible practice, and it is unlikely that the use of SPs or anything else will help. What Rob misses is that you can use the very fine-grained user-level security allowed by SQL Server to prevent people from doing a SELECT or DELETE against all tables. Frans mentions the use of Views as an alternative for allowing certain users access to only certain columns of a table.
Unfortunately, this does not always address the problem of allowing users access only to certain rows of a table. For instance, if you have an Employee table, you can grant access to SELECT from a view that shows demographic information, while not showing salary information. More difficult is restricting the view to only some rows of the Employee table. If you have four regions, it might be reasonable to have a vwEastEmployees, vwCentralEmployees, and so on. If, however, you have dozens of small regions, creating all those views and maintaining the security on them is not fun.
I have a number of situations in which users can have access to only certain rows of a database. The rules covering which rows a user can access are often complex and best managed in procedural code, or at least not easily handled in straight SQL Set-based code. In these situations, placing this logic in an SP (and denying any access to the bare tables) is very helpful.
One of the most damaging arguments raised in defense of SPs is that they somehow magically prevent SQL injection attacks. From Rob's post:
Additionally, stored procedures are a counter-measure to dangerous SQL Script injection attacks, a susceptibility that applications using embedded SQL are more vulnerable to.
Sorry, but this is just not true. Using SPs make it more likely that you will pass parameters the right way, but there is no guarantee. For instance, this is some code I recently read answering a question on http://www.asp.net :
strsql = "EXECUTE findtitle '" & textboxtitle.text & "'"
objCmd = New SqlCommand(strSQL, objConn)
Trust me, even though this hapless programmer is using stored procedures, the application is susceptible to SQL injection attacks.
In the Microsoft SQL Server environment, SQL injection attacks can be prevented using parameters, with or without SPs. Earlier I said this is a damaging argument, and by that I mean it is damaging to programmers who cannot use SPs: They will leave their applications more vulnerable to attack than they should because of this bit of misinformation.
Security: No SP advantage, unless restricting access to rows in complex ways.