0

I wrote a stored procedure in 'test' database.This procedure will create temp table to tempdb.

I found when SQL service restart. It lost my user in tempdb and procedure has no permission to access in.

I need to add user again in tempdb: security-> user.

User Setting:

Database-Level Roles : public & db_owner

Fixed-Database Roles : db_owner

When service restart I want my user setting keep it. Is anything can to that?

White Gallime
  • 11
  • 2
  • Does this answer your question? [Why does my tempdb reset permissions when the server is rebooted?](https://stackoverflow.com/questions/5315897/why-does-my-tempdb-reset-permissions-when-the-server-is-rebooted) – underscore_d Jun 23 '20 at 09:33

2 Answers2

1

Objects are in tempdb only persist until they are dropped or the instance is restarted. This is by design. "tempdb" means "Temporary DataBase". The objects within are inherently temporary, as the database is rebuilt each time the server restarts.

From TempDB Database:

Operations within TempDB are minimally logged so that transactions can be rolled back. TempDB is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in TempDB to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on TempDB.

If you want permanent objects, you should create them in a user database, such as your test database.

Thom A
  • 88,727
  • 11
  • 45
  • 75
1

When SQL Service restarts then TempDB automatically re-created.Means Everytime it copies the structure of Model DB.If you want the user should be existed in TempDB even after restarted then you need to create the user in Model DB.But for security reasons i would suggest you to create the user in test DB only.

K Viswagna
  • 99
  • 5