What permissions do temp stored procedures use?

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.

What permissions do temp stored procedures use?

Rate

Share

Share

Rate