0

An application vendor representative asked me to grant dbowner access to tempdb for their application login; to be able to create objects in tempdb without "#" or "##" prefixes. I tried to convince him to forget asking for direct tempdb access by arguing that granting direct access may interfere with SQL Server engine internal operations and prevent tempdb cleanup processes to do their jobs correctly. And also there is another drawback on SQL Service restarts which causes any permission setting on tempdb to revert to defaults.

Is there anything that I might miss in this regard?

Farshid SQLServer
  • 3
  • 1
  • 2
    You should not use tempdb for persistent storage, everything in it is disposable. As per the Microsoft [tempdb database](https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15) documentation, _`tempdb` is re-created every time SQL Server is started so that the system always starts with a clean copy of the database_ and _Backup and restore operations are not allowed on `tempdb`._ In my opinion you should change application vendors just as quickly as you possibly can. – AlwaysLearning Dec 03 '21 at 09:45
  • If an application requires its own working database and doesn't wish to use "temporary" objects then it should be responsible for its own private database. Eg SSRS needs to create a large number of temporary/volatile objects that are not part of the application or require inclusion on a backup solution, so it has its own *ReportServerTemp* database so it can reliably retain data following a restart. – Stu Dec 03 '21 at 09:56
  • `db_owner` is a pretty bad idea, at the most you could give them `db_ddladmin`. But as mentioned, everything gets wiped anyway, so it's unclear what they are trying to achieve. You can change the `model` database, which is used to recreate `tempdb` but that's probably an even worse idea – Charlieface Dec 03 '21 at 10:09
  • @Stu , Thanks for providing good example of not using tempdb and having your own temp storage – Farshid SQLServer Dec 03 '21 at 11:00

0 Answers0