Why does a SQL Server user with datareader permission to a user database also have permission to drop/create local/global temp tables?

Question

I have created a SQL Server user with datareader permission to a user database.

Why does this user automatically have permission to drop/create local/global temp tables?

The problem is that any user can mess around with global temporary tables created by other users or applications.

Does this answer your question? [how to prevent SQL Server users from creating temp tables?](https://stackoverflow.com/questions/50066973/how-to-prevent-sql-server-users-from-creating-temp-tables) — user2864740, Dec 31 '21 at 06:27
Seems like it is not possible without removing tempdb access entirely — https://dba.stackexchange.com/q/294631 , https://dba.stackexchange.com/q/176614 — user2864740, Dec 31 '21 at 06:29
Bear in mind that `tempdb` is used for things like worktables and sort/hash spills, so you really don't want to revoke access. Global temp tables are generally a bad idea anyway, but if necessary you can `DENY ALTER` on that table — Charlieface, Dec 31 '21 at 11:18
I presume the actual answer to the question is just that this is how it was designed > 20 years ago and it hasn't been problematic enough to warrant changing. — Martin Smith, Dec 31 '21 at 14:47

Venkataraman R · Answer 1 · 2021-12-31T06:36:47.830

1

Basically, the temporary tables the datareader user is creating is created on tempDB database. By default, any user can create objects in tempdb database, unless explicitly denied.

Refer to tempdb permissions reference

Any user can create temporary objects in tempdb. Users can access only their own objects, unless they receive additional permissions. It's possible to revoke the connect permission to tempdb to prevent a user from using tempdb. We don't recommend it because some routine operations require the use of tempdb.

For example, in the below sql code, I am creating a user , which is just having public role. It is also able to create temporary tables. public database role is given by default, when you add a user for a login in a database. Read more about public role

use master
go
create database testperm
go
create login testpermlogin with password = 'Somecomplexpassword123#'
go
use testperm
go
create user testpermuser for login testpermlogin
go
execute as user ='testpermuser'
go
select USER_NAME()
go
create table #test(a int)
go

edited Dec 31 '21 at 06:36

answered Dec 31 '21 at 06:28

Venkataraman R

12,181
2
31
58

But is the local and global temp db created by user1 accessible only to user1 or to all users? – variable Dec 31 '21 at 06:29
It is only available to the user1. – Venkataraman R Dec 31 '21 at 06:33
@variable Local temp tables are visible only to the current session. (At least on the surface.. admin logins can do some digging.) – user2864740 Dec 31 '21 at 06:34
@VenkataramanR - global tables are shared by all users. I just tested this. – variable Dec 31 '21 at 06:46
@variable, yes. I meant about the local tables. sorry for confusion. – Venkataraman R Dec 31 '21 at 06:47

David Browne - Microsoft · Answer 2 · 2021-12-31T14:31:54.227

0

The problem is that any user can mess around with global temporary tables created by other users or applications.

This is just one more reason not to use global temporary tables. They aren't very useful, and they are extremely rarely used.

I can't think of a scenario where it wouldn't be better to use a local temp tables, a permanent tables in TempDb, or a regular table in a user database.

edited Dec 31 '21 at 14:31

answered Dec 31 '21 at 14:22

David Browne - Microsoft

80,331
6
39
67

Why does a SQL Server user with datareader permission to a user database also have permission to drop/create local/global temp tables?

2 Answers2