There are two main (security) reasons to do this, above and beyond just using parameterized queries:
- Parameter type enforcement
- Least privilege.
The principle of Least Privilege requires you to allow any entity (user or application) access only to whatever it needs to do the defined task. If you don't restrict the webapp only to the SPs, the application could potentially execute any arbitrary query.
Note that this is relevant in two situations: preventing an attacker, that managed to find a vulnerability in your application (either SQL injection, or any other vuln that could allow him to execute code), from running malicious SQL queries; and, much lesser risk, developers that are looking for insecure, unapproved shortcuts (or even malicious developers).
Granting only EXECUTE privileges on the required SPs, will stop the application from running any query that was not predefined.
Wrt enforcing parameter types, while it is possible to implement this in other ways, this brings the type enforcement to the database, but before it hits the db server. I.e. using the types that are actually defined on the database, and without accidentally skipping a parameter.
Note that in order to do this properly, and avoid some common mistakes, you want to:
- define a specific user account for the ASP.NET application
- assign the account to a custom DB role
- remove the account from all other roles, such as
- grant EXECUTE privileges to the custom DB role you created
- remove all other privileges on the SPs, tables, and other DB objects. This includes default "public" roles, and so forth.
- ensure that the custom DB role has no other privileges.