Odd Security Behavior in TempDB

This is one of those posts that I fell into because someone was doing something horribly wrong. I found some code yesterday that was creating objects in TempDB using the following DDL.

Create table tempdb.dbo.table_name

Please never do that—if you are going to create a temp table, only create using # or ## as the prefix, so it behaves normally. Ok, rant over—now on to the issue I’m seeing. My environment is running SQL 2012 SP1. I create a couple of very basic procedures.

/*Don't Ever Do This*/

create procedure test1 as
select * into tempdb.dbo.perm from sys.databases
update tempdb.dbo.perm set name=’Test’
drop table tempdb.dbo.perm;

/*This is the right(er) way*/

create procedure test2 as
select * into ##perm from sys.databases
update ##perm set name=’Test’ drop table ##perm;

So I create a user with public (and exec on the two above procedures) in the destination database for the stored procedure. Additionally, the user was granted DDL_ADMIN in TempDB.

So when I run test1 I get the following:

(5 row(s) affected)

Msg 229, Level 14, State 5, Procedure test1, Line 5

The UPDATE permission was denied on the object ‘perm’, database ‘tempdb’, schema ‘dbo’.

The user can do the select (and the drop table—it has DDL_ADMIN, note the user doesn’t need to be granted DDL_Admin to work with normal temp tables) , but it can’t do the update.

When I run test2, I get the following:

(5 row(s) affected)

(5 row(s) affected)

That does the select into, executes the update and drops the table. Logically, I would think the update would fail, as the user doesn’t have db_datawriter in TempDB.

On Twitter Daniel Taylor (b|t) (thanks much!, btw) pointed out the following Microsoft documentation which offers some insight.

I suspect if an object gets created as # or ## those rules apply, but if it gets created as a normal table, typical SQL Server security rules apply. I’ve investigated the sys.database_permissions view, and looked in ResourceDB, but I haven’t found any complete confirmation.

About jdanton1
A DBA, cyclist, cook (who likes to play chef occasionally)

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: