asked on

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?

Yveau

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 ...

Chris Mangus

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.

Yveau

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 ...

saintsairforce

ASKER

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.

ASKER CERTIFIED SOLUTION

Yveau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial