What permissions do temp stored procedures use?6
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.
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.
(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
(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
(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.
Category: Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL | Tags: Microsoft SQL Server, security, T-SQL
6 thoughts on “What permissions do temp stored procedures use?”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] Kenneth Fisher wants to see how security for temporary stored procedures works: […]
Here you haven’t given execute permission on temp stored procedure so obviously you’ll get err
You’ll notice that the error isn’t an execute error. That’s because much like querying a temp table you automatically have the permission.
[…] 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) […]
In case anyone finds the following diagram useful for explaining ownership chains,
Very nice. Thanks for sharing 🙂