Changing the security context with Dynamic SQL [T-SQL]

I’ve always found that understanding the SQL security model can be a finicky affair; that’s no criticism, its more to do with my reluctance to actually spend time immersing myself in it – I generally know what I need to know and that’s all. I remember back when I was first learning T-SQL I found the whole area of security around dynamic SQL difficult to grok until I saw an actual example of it and I figured it might be useful to anyone in the same position if I were to share a code sample demonstrating these peculiarities. Hence this blog post.

The particular vagary that I want to demonstrate is that where the use of dynamic T-SQL inside a stored procedure will change the security context under which that dynamic T-SQL is executed. Code inside a stored procedure will execute as the owner of the stored procedure until some dynamic T-SQL is encountered at which time the security context switches to that of the user that called the stored procedure. Allow me to demonstrate:

  --Create demo DB with objects
  SET NOCOUNT ON;
  USE MASTER
  BEGIN TRY
      DROP DATABASE DynSQL;
  END TRY
  BEGIN CATCH
  END CATCH
  CREATE DATABASE DynSQL;
  GO
  USE DynSQL
  GO
  CREATE SCHEMA [sch];
  GO
  CREATE TABLE [sch].[foo] ([bar] INT);
  GO
  --===The important bit============================
  CREATE PROC [sch].[demoproc] AS
  BEGIN
      --The following statement will be executed as [sch]
      SELECT * FROM [sch].[foo];
      DECLARE @sql NVARCHAR(MAX) = 'SELECT * from [sch].[foo];';
      --The dynamic SQL in @sql will be executed as the user that called [sch].[demoproc]
      EXEC sp_executesql @sql;
  END
  GO

  CREATE LOGIN [u] WITH PASSWORD = 'p@ssw0rd';
  CREATE USER [u] FOR LOGIN [u];
  GRANT EXECUTE ON [sch].[demoproc] TO [u]

Read more: SQLBlog.com

Posted via email from .NET Info