What permissions do temp stored procedures use?

4

June 19, 2019 by Kenneth Fisher

In a performance tuning class I recently took with Brent Ozar (b/t) he mentioned temporary stored procedures and an interesting question came up. As it happened I’d seen these before in a blog post by Kendra Little (b/t) but never thought of this particular question.

The question:

With normal stored procedures there is something called ownership chaining. Without going into a lot of detail about what it means, let’s say that you run a stored procedure. SQL is going to check the permissions to see if the stored procedure can update that table right? Well, who’s permissions? Yours? Well, yes, if you have permissions you are fine. But you won’t always. If you don’t then SQL is going to check the owner of the stored procedure (dbo?) and see if they also own that table. If so then we’re golden, perform the update. That might seem scary but it’s pretty normal.

What was scary (at least to me) is the question “How is that handled for a temp stored procedure?” I mean creating a temp stored procedure is a public permission, everyone can do it. If that temp sp is owned by dbo then anyone could create a temp sp and do anything right? Fortunately no.

To start let’s create a user to work with and grant it no permissions.

CREATE LOGIN TempSP_test WITH PASSWORD = 'TempSP_test', CHECK_POLICY = OFF;
CREATE USER TempSP_test FROM LOGIN TempSP_test;

Then, in another query window connect as TempSP_test. I should probably point out there is a table called Table1 that I created for something else. It has columns Id (int, identity), Descrip (varchar), Val (varchar).

Just to confirm let’s make sure we can’t insert into Table1 or even see any tables.

SELECT name FROM sys.tables;
INSERT INTO Table1 VALUES ('',''); -- Yea, no values. I'm lazy. Sue me.

name
——————————–

(0 rows affected)

Msg 229, Level 14, State 5, Line 7
The INSERT permission was denied on the object ‘Table1’, database ‘Test’, schema ‘dbo’.

Nope. No go. Exactly as expected. Ok, how about using temp SPs.

CREATE OR ALTER PROCEDURE #TableList
AS
SELECT name FROM sys.tables;
GO
CREATE OR ALTER PROCEDURE #TableInsert
AS
INSERT INTO Table1 VALUES ('',''); 
GO
EXEC #TableList;
GO
EXEC #TableInsert;
GO

name
——————————–

(0 rows affected)

Msg 208, Level 16, State 1, Procedure #TableInsert, Line 3 [Batch Start Line 8]
Invalid object name ‘Table1’.

Pretty close to the same error messages. Just to be 100% certain of how this is working let’s grant the user the permissions needed to see and insert into Table1.

GRANT SELECT, INSERT TO TempSP_test;

Obviously run that under another id (in a different session). Then run this next bit under the original TempSP_test session. I want to use the same set of temp stored procedures that we created earlier. Just to be sure nothing is checked on creation (not that I expect it to, but still).

EXEC #TableList;
GO
EXEC #TableInsert;
GO

name
——————————–
Table1

(1 row affected)

(1 row affected)

There you go. The access to other objects depended entirely on the users permissions. There doesn’t appear to be any ownership chaining at all. Well, possibly ownership chaining for objects within tempdb but I’m less worried about that.

4 thoughts on “What permissions do temp stored procedures use?

  1. […] Kenneth Fisher wants to see how security for temporary stored procedures works: […]

  2. Here you haven’t given execute permission on temp stored procedure so obviously you’ll get err

  3. […] that we’ve talked about how they act (you can check out how security seems to work on them here) why would you want to use them? Well, I was initially introduced to them by Kendra Little (b/t) […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,531 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: