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)
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.
8 thoughts on “Security Questions: What permissions are required to create temporary tables?”
What if I create a “billion record” temp table? No problem?
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.
LikeLiked by 1 person
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.
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.
Could a database level trigger or event be used to block the creation of a temp table or even immediately drop it?
No, database triggers on CREATE_TABLE; whether on the user database or tempdb, will not impact the creation of temporary tables.
Comments are closed.