deny permission to create temporary tables

Dear All,

This is my first post to this forum.

I would like to know if there is any way to restrict users from creating temp tables.

Problem: I am facing problems with lots of temporary objects getting created in my database. The users have read-only access to the database for adhoc-querying purpose through QA. Yet they are able to create temporary tables in tempdb database taking lot of resources on tempdb disk causing abnormally high growth of tempdb.

Thanks in advance.

Best Regards,

Chetan Jain

Just by executing a query, users may be using space in TempDb -that is what it is designed for. Query execution may, totally on its own volition, create temporary tables in TempDb. TempDb can growth large if the users are executing queries that require a lot of temporary storage to gather data to work with -JOINs with large resultsets, large resultsets to be sorted, etc.

Are the users creating tables 'temp' tables (starting with [#], or [##]? Or, are they creating tables?

Normally, the users' temp objects are removed from TempDb when the user connection is closed.

Perhaps the real issue is trying to determine how much space TempDb requires in order to support your users query needs, and then giving TempDb adaquate disk space.

|||

You can not stop any user from creating temprory objects in tempdb. ofcourse you can stop creating permanent table in tempdb by DDL trigger. but DDL trigger can not sense temp table.

create TRIGGER db_trg_RestrictTableChanges

ON DATABASE

FOR CREATE_Table, ALTER_Table, DROP_Table

AS

SET NOCOUNT ON

rollback

the above mentioned trigger will stop creating permanent tables in tempdb but even this can not stop temporary table

Madhu

|||Madhu's suggestion is certainly a valid one, but, unfortunately, only works in SQL 2005 (and higher).|||

Thanks for the information!. The real issue is denying explicit statements like "create table #temp" or "create table ##temp"

Best Regards,

Chetan

|||

As Madhu indicated, you can't even deny creating temp tables in SQL 2005 using the new DDL Triggers -and you also can't do so in SQL 2000.

Just make sure that the users are logging out, and then their connection will be cleared, and the space used for any temp tables will be released.