mbova, here's the deal on security.

If we build stored procedures that contain only static SQL statements, we can take advantage of ownership chains. For instance, dbo owns the stored procedure and dbo also owns the tables and views referenced by the stored procedure. Because all the objects have the same owner, SQL Server will only check security when the stored procedure is executed. It makes the assumption that since the owner of the stored procedure also owns the objects referenced, the owner intended the person with execute rights on the stored procedure to have the appropriate rights to carry out the SQL statements within the context of the stored procedure. So if I don't normally have DELETE rights on a table, but a stored procedure I have the right to execute does a DELETE on the table, SQL Server will allow the DELETE through the stored procedure. If however, I were to try and execute a DELETE separate from the stored procedure, SQL Server will balk and prevent me from doing so. As a result, we can strictly control how data is accessed and modified. We simply give execute rights on the stored procedures and do not grant any rights on the tables and views.

When dynamic SQL enters the picture, however, things change. Even if I choose to execute a dynamic SQL statement from within a stored procedure, SQL Server will execute that dynamic SQL statement in a new context. It will be executed outside the context of the stored procedure, basically as an ad hoc query. SQL Server will check security with respect to the dynamic SQL statement, something we avoided in the static SQL inside a stored procedure example. Ownership chains become irrelevant with respect to the dynamic SQL statement.

That means if the DELETE is contained in a dynamic SQL statement and I don't have rights to DELETE from the table, SQL Server will balk. So what has to happen is where previously a user did not have to be granted DELETE permissions on the table, he or she now does. That means that the user can access and modify the data outside a stored procedure, in any manner rights allow.

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/