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?

Improve this question
4
  • 2
    You should not use tempdb for persistent storage, everything in it is disposable. As per the Microsoft tempdb database 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. Commented Dec 3, 2021 at 9: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. Commented Dec 3, 2021 at 9: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 Commented Dec 3, 2021 at 10:09
  • @Stu , Thanks for providing good example of not using tempdb and having your own temp storage Commented Dec 3, 2021 at 11:00
Related questions
1
MSSQL Server 2005 tempdb problem
13
Why does my tempdb reset permissions when the server is rebooted?
1
SQL Server 2005 user-defined data type, tempdb and permissions
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.