My suggestion is: avoid stored procedures at all, unless you have very special needs of performance on some complicated operations.

I know that here, at SQLServerCentral, many people are skilled DB administrators, and they like s.p. that give them much power. But if you are a developer, like me, it is better not to use them for the following reasons:

- Portability: there are many types of DB out of there, tomorrow you could find a customer who wants to run the application with Oracle, MySql, Db2, Access, ..., or even your single customer can change his mind about which is the best Db suitable for his needs.

There is a myth around that tells "use SP as much as you can" but I think this is just propaganda by the database producers. If you use s.p. then you can't change Db type easily, and this is exactly what they want.

SQL is a standard, s.p. are not.

There are some subtle differences in SQL syntax among different producers, but if you program in a smart way, you can work with them. S.p. instead must be completely rewritten.

- Maintenance: if business logic change, you probably need to change something into the code and something into the DB structure; it is easier to have only one place where to look for, the application code. If you use s.p. you should always check all s.p. code if you need to make a change.

- Security: the customer decides who can execute what, by assigning each user to one or more usergroups. You, the developer, decide which usergroups can execute what, by identifying the user.

I suggest you keep tables for users, usergroups, and rights, and avoid using DB users for anything other than distinguish db administrators from normal db users.

When the user is logged in you know which rights he has: for example, if your application has a pushbutton for "Delete all records" and you want that only some users can push it, you will define a right for this and your code will disable or hide that button if the user hasn't the right.

Someone says "dba can determine who can execute what", but what does this means ? That if you, by mistake, let a user run a procedure that it is not allowed to, the user will receive a difficult to undestand error. This is not the best solution, the best solution is that the application does not allows users to do things they are not allowed to.

I definitely think that s.p. should be reserved to very special needs, and I think this happens not more than 1 % of the times you need to do a Db operation from a normal application.

Bye

Francesco Muzul

P.S. 1: I am a C++ developer with 25 years of experience, working with SQLServer since 1995, but also with Oracle, Access, SQLite and others.

P.S. 2: sorry for my imperfect english, I'm italian.