SECURITY Specifying Execution Context for Procedural Code In

SECURITY Specifying Execution Context for Procedural Code In previous versions of SQL Server, cataloged procedural code always ran within the security context of the caller, as explained earlier in the section on ownership chaining. This is a good strategy for the most common case for example, when you want to allow users access to tables through stored procedures without giving them access to the base tables. However, it is not always what you want. Take, for example, a stored procedure that executes dynamic SQL composed by concatenating strings. This does a type of indirect parameterization of the table name. This is necessary because you may want to build a query with a table name as a parameter. this won t work CREATE PROCEDURE count_rows(@name NVARCHAR(50)) AS SELECT COUNT(*) FROM @name GO this will the dynamic SQL executes in the caller s context CREATE PROCEDURE count_rows(@name NVARCHAR(50)) AS EXECUTE( SELECT COUNT(*) FROM + @name) GO SQL Server 2005 now allows you to specify that procedural code execute in a different execution context. There are three reasons you might want to do this. You want dynamic SQL to execute in the context of the creator of the stored procedure, as static T-SQL would. Since data access code in CLR procedures (through the SqlServer data provider discussed in Chapter 4) is effectively dynamic SQL, you might want this code to execute in the context of the creator of the stored procedure as well. You want to evaluate ownership chains in the context of the creator of the stored procedure rather than the caller of the procedure. You choose the execution context on a per-procedure basis when you create the procedure, using the EXECUTE AS parameter. Execution context can also be set on user-defined functions, except for inline table-valued user-defined functions. Examples are shown in the following code.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Bookmark the permalink.

Comments are closed.