Security Questions: What permissions are required to create temporary tables?

As I mentioned in the introductory post, in the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

Temporary table question

The first question in the list is:

What permissions are required to create temporary tables?

I’ll be honest, this was a new one for me.  Not because it’s a bad question, but because I’ve never had problems trying to create temporary tables.  And also, I’ve never had complaints from users about not being able to create temporary tables.

That fact of the matter is, that as long as a user can connect to a SQL Server instance, the user is able to create temporary tables.  There are no special permissions.  A login with the most basic permissions has full rights to create and use temporary tables.  As it also turns out, there are no permissions for blocking or limiting temporary table creation.  This last bit would actually be useful, given the ability in other users I’ve seen before to fill tempdb with their temporary data sets.

Temporary Table Demo

Just to demonstrate that any login with access can create temporary tables, we’ll run through a quick demo.  Using the script in listing 1, create a new login on a SQL Server instance.

--Listing 1. Create new login

USE [master]
GO
CREATE LOGIN [TempTableUser] WITH PASSWORD=N'pass@word1'
, DEFAULT_DATABASE=[master]
, CHECK_EXPIRATION=ON
, CHECK_POLICY=ON
GO

Then connect to the SQL Server instance and run the create table script provided in listing 2.  You’ll see that without any issues, the user can create a temporary table.

--Listing 2.

CREATE TABLE #temp (Column1 INT)

Summary

Now this might not have been the deepest content on this blog ever, but it is a quick answer and proof that, as DBA’s we don’t have to do anything to allow temporary table access for users.  This question, though, makes me wonder, have you ever had issues with creating temporary tables?  Maybe someone out there has worked in a place where someone figured out how to block temporary tables create permissions.