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]
EXECUTE AS LOGIN = 'u';
EXEC [sch].[demoproc]; -- <-Will throw error "The SELECT permission was denied on the object 'foo', database 'master', schema 'sch'."
REVERT;
--===============================================
--Cleanup
DROP USER [u];
DROP PROC [sch].[demoproc];
DROP TABLE [sch].[foo];
DROP SCHEMA [sch];
DROP LOGIN [u]
USE MASTER
DROP DATABASE [DynSQL];
Let’s take a look at what happens. Firstly we get a (empty) resultset returned from the none-dynamic T-SQL inside the stored procedure because user [u] has got permission to execute [sch].[demoproc] and [sch].[demoproc] has got permission to select data from [sch].[foo]:
However over on the messages tab we see that we also get an error from the dynamic T-SQL even though its the same T-SQL statement (SELECT * FROM [sch].[foo];). This is because the security context switches to the caller (i.e. [u]) and that caller does not have permission to select from [sch].[foo]:
You can copy/paste the code above and execute it – it shouldn’t leave any objects lying around.
Hope this helps!
@Jamiet
P.S. The code above was formatted for the web using The Simple-Talk Code Prettifier.