Tag: tempdb

MSSQL SERVER: ERROR: CREATE TABLE permission denied in database ‘tempdb’

I was recently working with a client that was periodically but inconsistently getting a strange error when one of their SQL Server Agent jobs ran:

ERROR: CREATE TABLE permission denied in database 'tempdb'

Why is this error confusing? Well, at a minimum every database and every user has at least the public role. The public role is contained in every database, which includes system databases. It cannot be dropped and you cannot add or remove users from it. Permissions granted to the public role are inherited by all other users and roles because they belong to the public role by default.

We also know that any user with the public role can create temporary tables in tempdb. So, how on earth does this error make any sense?

Let me demonstrate.

It is still true that with only the public role a user can create both local and global temporary tables.

If you want to demonstrate this, create a new user in SQL and make sure that it only has the public role then try to create some tables like below:

Local temp table:

CREATE TABLE #test_table_local (
     product_name VARCHAR(MAX),
     list_price DEC(10,2)
 );

Global temp table:

CREATE TABLE ##test_table_global (
     product_name VARCHAR(MAX),
     list_price DEC(10,2)
 );

But watch what happens we we try to create a regular “permanent” table in tempdb:

CREATE TABLE test_table_permanent (
     product_name VARCHAR(MAX),
     list_price DEC(10,2)
 );

So, as you can see, the original statement is still true. The public role allows users to create temp tables in tempdb without any additional permissions. However, to create permanent tables in tempdb you will still need the CREATE TABLE permission.

In my client’s case it was a matter of a simple typo. One particular code path in the SQL Agent job triggered a code path containing the typo where a CREATE TABLE statement was missing a ‘#’ in front of the table name.

If you’re wondering why you would create a “permanent” table in tempdb, you wouldn’t in most cases but it does have some unique properties in that the table is available from other sessions (unlike local temporary tables), and it is not deleted automatically even if there are no connections to it (unlike global temporary tables). However, it does still get deleted when the instance is restarted (just like everything else in tempdb).

Reference Links & More Information

Server and Database Roles in SQL Server | The Public Role