You are here
Home > DBA

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

PadlockAs 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.

  • Brendan Morgan

    Could a database level trigger or event be used to block the creation of a temp table or even immediately drop it?

    • StrateSQL

      No, database triggers on CREATE_TABLE; whether on the user database or tempdb, will not impact the creation of temporary tables.

  • Chris F

    I’m wondering if public access gets removed from tempDB if that could cause a problem. Unfortunately I don’t have the time to test it right now.

    • StrateSQL

      There isn’t a public user, but there is a guest user. My attempts to drop the user were met with errors, so I’m assuming this isn’t something that would affect it one way or another.

  • Fantastic article ! You havemade some very astute statements and I appreciate the the effort you have put into your writing. Its clear that you know what you are writing about. I am excited to read more of your sites content.

    http://sqlservermasters.com/

  • SeekingHelp

    What if I create a “billion record” temp table? No problem?

  • Pingback: Tuesday Morning SQL Break – May 26, 2015 - SQL Server - SQL Server - Toad World()

  • Pingback: Monday Morning SQL Break – June 13, 2016 | Strate SQL()

Top