This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

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:

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.