Using Stored Procedures as Security Mechanisms

Administering SQL Server

Administering SQL Server

Using Stored Procedures as Security Mechanisms

Stored procedures, commonly used as an interface to perform complex activities, can be used to customize security permissions in much the same way as views.

For example, in an archiving scenario, stored procedures can copy data older than a specified interval into an archive table and then delete it from the primary table. Permissions can be used to prevent users from deleting the rows from the primary table directly or from inserting rows into the archive table without deleting them from the primary table. You can create a procedure to ensure that both of these activities are performed together, and then grant users permissions to execute the procedure.

See Also

CREATE PROCEDURE