Tag Archives: databases

Sprocs? Really?

I’ve been studying for my first SQL Server certification (70-431, if you’re curious).  I read the chapter today on “Implementing Stored Procedures.”  As I was reading, the following passage got my attention:

The permission delegation possible with stored procedures provides a powerful security mechanism within SQL Server.  If all data access – insertions, deletions, updates, or selects – were performed through stored procedures, users could not directly access any table in the database.  Only by executing the stored procedures would users be able to perform the actions necessary to manage the database.  And although users would have the permissions delegated through the stored procedures, they would still be bound to the code within the stored procedure…

How is this in any way desirable?  Who are you actually trying to keep the real data away from?  The only answer that is obvious to me is developers.  The author seems to be describing some personal utopia, where no childish developers could destroy what the mighty DBA hath wrought.

He says in an earlier section, “Even more important, stored procedures hide the structure of a database from a user…”  Hide the structure of the database?  Again, why?  How could developers make informed decisions about creating data access layers if they don’t even know what tables the data is on?

The author also says that using stored procedures lets you “…isolate database code for easy maintenance instead of requiring you to find hard-coded SQL statements throughout an application if you need to make changes.”  A worthy goal, certainly, but is the answer really to put the executable code in the database?  I would think that a well-crafted data access layer would do a much better job of this.  Conventional languages offer much more flexible language environments, and code changes are tracked by source control.  You can put stored procedure definitions in source control, but it’s in no way integrated into SQL Server.  It’s basically the same developer experience as checking in some unrelated text file to some arbitrary directory in your tree, which has has no real bearing on what’s actually running in the DB at the moment.

I’ve heard people talk about the database-centered approach espoused by MS, but this really makes it clear.  It’s as if the author feels that the best application would be one with as little application code as possible, basically a thin veneer over a relational database.  Since I’ve worked for the first three years of my career in a shop that used DB2 (with absolutely no stored procedures), I guess I haven’t even been offered this particular flavor of Microsoft Kool-Aid until now.  If this has been the Redmond gospel for a while, I can see why using OR mappers must feel like such as breath of fresh air to some people.

For what it’s worth, I’m planning on using ASP.NET MVC with Castle’s ActiveRecord for my next pet project, a little site I’m putting together for my mom’s fiancee.  I’ll be sure to record my impressions of that story.