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?
LVL 2
saintsairforceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.