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.