0

We'd like to prevent users from having the ability to create temp tables on production SQL Servers. It seems that simply being able to login to SQL Server includes permission to create temp tables. How can we revoke this permission without eliminating their login?

Eric B
  • 75
  • 6
  • 3
    Why would you want to stop such behaviour? – Thom A Apr 27 '18 at 16:54
  • 1
    I agree with Larnu, if you are giving them access to the server at all why do you want to restrict them from creating temp tables? Just dont give them access at all – Brad Apr 27 '18 at 16:56
  • 2
    I imagine this is an [xy question](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). Hopefully the OP will respond so we can understand their motives and make suggestions on their goals. – Thom A Apr 27 '18 at 17:02
  • 1
    There is no way to prevent this. Temp table creation just happens. It all takes place in the tempDB space, which is used for just normal SQL stuff as well as a sort of spool space for for intermediate result sets, sorting, and the like. It's necessary functionality for every day SQL that they are submitting. – JNevill Apr 27 '18 at 17:02
  • I think you're only giving some of the story here. What type of production server is this? Reporting? OLTP? How are the users accessing the server? What level of permissions do they have now? – SS_DBA Apr 27 '18 at 17:02
  • There's a lot I didn't mention, but I think JNevil's answer is sufficient. This is a production data warehouse server. A BI developer with read-only permission was analyzing some results of a recently-deployed SSIS package and created some global temp tables in the process, whereas she could've pulled the data to a dev environment but chose not to. During that time, the boss saw global temp tables and told us 1) don't do that, and 2) how can we prevent this in the future? All my research and testing showed temp tables are an inherent right for any login. – Eric B Apr 27 '18 at 17:46
  • Yes, the matter at hand is more a training issue. You can't stop people from creating then, but they need to learn when they shouldn't be (global temporary tables are especially awful). – Thom A Apr 27 '18 at 17:51

1 Answers1

1

I believe JNevill answered my question correctly:

There is no way to prevent this. Temp table creation just happens. It all takes place in the tempDB space, which is used for just normal SQL stuff as well as a sort of spool space for for intermediate result sets, sorting, and the like. It's necessary functionality for every day SQL that they are submitting.

I confirmed this with my own research and testing and nobody submitted a different answer.

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
Eric B
  • 75
  • 6