Using Stored Procedures to Manage SQL Server Security

Stored Procedures offer an opportunity to improve your database security. By denying read (SELECT) and write (INSERT, UPDATE, and DELETE) access to all database tables and providing only EXECUTE privileges to your stored procedures, you can ensure that your data can be accessed in a much more controlled, restricted medium. Ad hoc SQL no longer will be able to access your data. All data access must pass through stored procedures and be subject to any validation, security, logging, etc. that you care to implement at this layer.

Here's an example of how to do it.

1. Make a new login - in my case I'm using MyLogin. Check off the database that you want to allow this login access to -- in my case I chose -- Northwind. Then check off public for the database role.

2. By default, this login will not have permission to Insert, Select, Update or Delete records in any tables. Your data can now be accessed only via stored procedures that you have granted Execute permissions on. Test your new login in Query Analyzer by trying to select on a table in the database after you have logged in with the new username and password. This will generate an error as expected.

SELECT * FROM sysobjects

Results …

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'suppliers', database 'Northwind', owner 'dbo'.

3 . Grant permission to Execute all stored procedures for this new login. Create the Grant statements with the following SQL statement, copy the results, and run them in Query Analyzer while logged in as SA (not as the new login). Note: this SQL assumes you use some other prefix than "sp" to name your own stored procedures. SP is reserved for System Stored Procedures.

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs

Results …

GRANT EXECUTE ON usp_ins_AUDIT_FIELD TO MyLogin
GRANT EXECUTE ON usp_del_TRANSACT TO MyLogin
GRANT EXECUTE ON usp_upd_AUDIT_FIELD TO MyLogin
GRANT EXECUTE ON usp_ins_TRANSACT TO MyLogin
GRANT EXECUTE ON usp_del_AUDIT_LOG TO MyLogin

4. As you add new procedures re-run the Grant scripts to make sure the new login has Execute permissions on ALL stored procedures.