• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 962
  • Last Modified:

tempDB persistent permissions issue

I am working with SQL Server 2005. I have a user that is mapped to tempDB. I set the permissions to read and write. Everything works great. My issue is that every now and then we restart our server and as a result the settings for the permissions for tempDB disappear so the user ends up being locked out. Does anyone know how to make the permissions persistent?
0
saintsairforce
Asked:
saintsairforce
  • 3
1 Solution
 
YveauCommented:
With a restart of SQL Server (so as well with a reboot) tempdb gets recreate from the model database from scratch.
So it's actually a complete new database. You can't do what you want other than by creating a procedure that will automatically run on SQL Startup ...

Hope this helps ...
0
 
Chris MangusDatabase AdministratorCommented:
You could set the user permissions on Model as you want them to appear in TempDB.

The caveat is that any new database you create on this server will have the same permission by default.
0
 
YveauCommented:
That's bad, shame on MS !!!
When you set the user permissions to model only for a given user and you restart the service, that user gets the same permissions on tempdb ... where he/she didn't had those permissions before the restart !

I thought they would have figured that one out ... not !!!

BTW, any user has permissions to the tempdb by default. Just curious why you need to grant/map the user for the tempdb ...

0
 
saintsairforceAuthor Commented:
For what ever reason our DB requires read/write permissions for one user to the tempDB. This tempDB is just used to store Sessions. When we restart the server the sessions are lost as expected but the permissions are lost as well, which was not expected.
0
 
YveauCommented:
... it actually could have been expected, because as I said, tempdb gets dropped and recreated from scratch at a restart of SQL Server. The trick with the model db works ... however the user granted permissions to the model db will get permissions on EVERY new database created ... keep that in mind !!!

So if I were in your shoes I would just make a note of it and keep the script to grant the permissions near. And just run that script at every restart ... which shouldn't that often ... right ?

Hope this helps ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now