What permissions are required for temporary tables?

Managing permissions is a constant issue for Database Administrators, but rarely do DBAs consider permissions for tempdb. Everybody’s looking for something, but how often do you get requests for “access to read and write in the tempdb database”? Like…never?

OK, but what if you were asked the subject of this post in a job interview? Even if you’ve worked with SQL Server for ages, would you know how to answer this? Moreover, would you know why the answer should give you some concern?

Hold your head up, because I’ll start answering these questions now.

The short answer to the title in the post is ”nothing special,” but let’s take a deeper look at what that means.

Some of them want to use your tempdb

Here’s what the documentation says about tempdb permissions:

Any user can create temporary objects in tempdb. Users can access only their own objects, unless they receive additional permissions.

So basically, all users can use tempdb. Huzzah, you don’t have to do anything as a DBA to grant these permissions!

However…just because I answered that question doesn’t mean you should stop reading. Let me show the perils of this open access to tempdb.

Some of them want to abuse your tempdb

Let’s take a look at how any simple user with basically no permissions other than connecting to the database can cause real problems in your tempdb.

First, let’s create a simple user that has no explicit permissions whatsoever.

USE [master]
	GO
	CREATE LOGIN [SimpleUser] WITH PASSWORD = N'mischief'
		, DEFAULT_DATABASE = [master]
		, CHECK_POLICY = OFF;
	GO

Now, open a new connection to your instance using that new “SimpleUser” and run the following:

SET NOCOUNT ON;

		USE tempdb;

		CREATE TABLE #Junk (JunkID INT, JunkText VARCHAR(MAX));

		DECLARE @JunkID INT = 1;

		WHILE @JunkID <= 10000 BEGIN

			INSERT #Junk(JunkId, JunkText)
			SELECT @JunkID, REPLICATE('blah', 1000);

			SET @JunkID +=1;

			END;

Then run this, back as your admin account in a separate window to see how much space the SimpleUser just consumed.

SELECT SUM(user_object_reserved_page_count)/128.0 AS HowMuchMB
		FROM sys.dm_db_file_space_usage;

Look ma, 40 MB!

Now, the point of this exercise is not so much about the 40 MB space as it is this: by default, any user can consume any tempdb space, limited only by either maximum file size or available drive space.

Do I have your attention?

Moving on

Understand this same principle applies to table variables as well. You could run a variation of the previous script using a table variable instead of a temp table and get roughly the same result. Run this as your sysadmin account, since the table variable will go poof when the script is completed.

SET NOCOUNT ON;

		USE tempdb;

		DECLARE @Junk TABLE (JunkID INT, JunkText VARCHAR(MAX));

		DECLARE @JunkID INT = 1;

		WHILE @JunkID <= 10000 BEGIN

			INSERT @Junk(JunkId, JunkText)
			SELECT @JunkID, REPLICATE('blah', 1000);

			SET @JunkID +=1;

			END;
		SELECT SUM(user_object_reserved_page_count)/128.0 AS HowMuchMB
		FROM sys.dm_db_file_space_usage;

What is this - roughly another 40MB? Who am I to disagree.

These are just the obvious consumers of tempdb. Remember other things like cursors, internal worktables for spools and sorts, and memory spills can also consume tempdb space as well. Heck, I recently had a long-running query cause version store to fill up a tempdb.

Everybody’s looking for something

As I asked at the beginning of this post, if you’re a DBA this kind of thing should give you a bit of real concern. I’ve seen plenty of users with db_datareader permissions to just ONE database discover the sweet dreams of temporary tables or table variables and abuse it all the way into filling up tempdb.

I’ve found that resolving these issues is best handled with a little education, as just about everyone who fills up your tempdb is naïve as to its limitations. However, if you find a particular user or group to be abusive to tempdb, there are ways to curb that behavior such as revoking the connect permission to tempdb (although personally, I’ve never decided to do this.)

Oh, and if you’re NOT a DBA, well, thank you for reading this far, and please be VERY VERY CARFEUL with how much data you’re dumping into your temporary tables and table variables.