Stored Procedures Permissions Management

Published on Tuesday, March 29, 2011

Simply writing stored procedures isn't enough to adequately secure your application. You should also consider the following potential security holes.

*      Grant EXECUTE permissions on the stored procedures for database roles you want to be able to access the data.
*      Revoke or deny all permissions to the underlying tables for all roles and users in the database, including the public role. All users inherit permissions from public. Therefore denying permissions to public means that only owners and sysadmin members have access; all other users will be unable to inherit permissions from membership in other roles.
*      Do not add users or roles to the sysadmin or db_owner roles. System administrators and database owners can access all database objects.
*      Disable the guest account. This will prevent anonymous users from connecting to the database. The guest account is disabled by default in new databases.
*      Implement error handling and log errors.
*      Create parameterized stored procedures that validate all user input. Treat all user input as untrusted.
*      Avoid dynamic SQL unless absolutely necessary. Use the Transact-SQL QUOTENAME() function to delimit a string value and escape any occurrence of the delimiter in the input string.

above content from MSDN