tempDB persistent permissions issue

Posted on 2007-10-16
Last Modified: 2008-09-20
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?
Question by:saintsairforce
    LVL 18

    Expert Comment

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

    Expert Comment

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

    Expert Comment

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

    LVL 2

    Author Comment

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

    Accepted Solution

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

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Another way of doing this SQL 8 32
    Insert statement is inserting duplicate records 15 48
    SQL Select * from 6 25
    sql help 5 42
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now