Execution Plan Cache Behavior - SQL Server Query Performance Tuning - page 293

Additional Benefits of Stored Procedures
Some of the other benefits provided by stored procedures are as follows:
• The application is isolated from data structure changes : If all critical data access is made
through stored procedures, then when the database schema changes, the stored procedures
can be re-created without affecting the application code that accesses the data through the
stored procedures. In fact, the application accessing the database need not even be stopped.
• There is a single point of administration : All the business logic implemented in stored
procedures is maintained as part of the database and can be managed centrally on the
database itself. Of course, this benefit is highly relative, depending on whom you ask. To get a
different opinion, ask a non-DBA!
• Security can be increased : User privileges on database tables can be restricted and can be
allowed only through the standard business logic implemented in the stored procedure. For
example, if you want user UserOne to be restricted from physically deleting rows from table
RestrictedAccess and to be allowed to mark only the rows virtually deleted through stored
procedure MarkDeleted by setting the rows' status as 'Deleted' , then you can execute the
DENY and GRANT commands as follows:
IF (SELECT OBJECT_ID('dbo.RestrictedAccess')
) IS NOT NULL
DROP TABLE dbo.RestrictedAccess;
GO
CREATE TABLE dbo.RestrictedAccess (ID INT,Status VARCHAR(7));
INSERT INTO t1
VALUES (1,'New');
GO
IF (SELECT OBJECT_ID('dbo.MarkDeleted')
) IS NOT NULL
DROP PROCEDURE dbo.MarkDeleted;
GO
CREATE PROCEDURE dbo.MarkDeleted @ID INT
AS
UPDATE dbo.RestrictedAccess
SET Status = 'Deleted'
WHERE ID = @ID;
GO
--Prevent user u1 from deleting rows
DENY DELETE ON dbo.RestrictedAccess TO UserOne;
--Allow user u1 to mark a row as 'deleted'
GRANT EXECUTE ON dbo.MarkDeleted TO UserOne;
This assumes the existence of user UserOne . Note that if the query within the stored procedure MarkDeleted
is built dynamically as a string ( @sql ) as follows, then granting permission to the stored procedure won't grant any
permission to the query, since the dynamic query isn't treated as part of the stored procedure:
CREATE PROCEDURE dbo.MarkDeleted @ID INT
AS
DECLARE @SQL NVARCHAR(MAX);