• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 955
  • 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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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