Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Unanswered: Public user; tempdb permissions

    We have financial application that uses SQL 2000 as the backend database. As part of the setup we were told that the "public" user account needed "full permissions" to the tempdb.

    So, I right-click on the tempdb and go the the properties and then to the permissions and give "public" rights for all options.

    Everytime the system is restarted after MS critical updates or any other reason the "public" user is reset back to the default settings and I have to go back in and reset the permissions for "public" in the tempdb.

    Anyone know why this is happening? We have a production box and a development box and they both do the same thing.

    Thanks for any insight.

  2. #2
    Join Date
    Sep 2003
    Location
    Columbia, MD USA
    Posts
    95
    just curious, but did they give you any reason for having to do that?

    I can't think of any reason that you'd have to. Have you tried just leaving it alone? If so, do the users get any kind of errors?

    also curious as to the application that's requiring you to do something like that
    drew
    ------------------
    http://wjtechnologies.com

  3. #3
    Join Date
    Dec 2002
    Posts
    5
    The application is Portia from Thomson Financial. It is a portfolio management app.

    If the public user does not have rights to the tempdb then some reports that users run will return no data and other problems like funny application error messages.

    They didn't give a reason why this is required, but I am guessing it is a cover for poor application design.

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    your guess is correct. my hunch is they create permanent objects in tempdb (each user upon being added to a database is automatically enrolled into public group of that database. in properties window of tempdb they require permissions given to public to create all objects, not sure about other functions like backup, etc.)

  5. #5
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Wow

    You can't backup a TempDb !! All users have permissions in a temp db and only they can access the temp table created with "#" Prefix. However , a Permanent temp table created by one user is owned by that user and behaves like any other table in a regular database . All those permissions and object would be gone after the SQL is restarted because tempdb , as the name says is a temporary database and is recreated each time SQl Server restarts
    Last edited by aashu; 10-22-03 at 16:48.

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    a permanent object cannot be created in a database, including tempdb, unless a user is explicitly granted permissions to create that type of object or the user belongs to the database group/role or the server fixed role that has those permissions. in this discussion the vendor requires public to have permissions to create certain objects in tempdb. of course it gets recreated every time the service restarts! that's not the question! the question is how to make it so that granting permissions will not be the chore to do every time it happens. i'd go with a startup procedure that would grant the required permissions to public.

  7. #7
    Join Date
    Sep 2003
    Location
    Columbia, MD USA
    Posts
    95
    I think that besides creating a startup procedure to grant the rights you need to call that company and tell them that thier developers need to take a look at what they are doing and add 'reboot server and test functionality again' to thier QA process. I sure hope that's an alpha or beta release of that software... what a freakin' nightmare.


    why in the world wouldn't they just create the objects in the database they are working in? or store those objects in another database if they had some concern about the objects being there.

    sorry to sound ranty... that kind of stuff just plain bothers me
    drew
    ------------------
    http://wjtechnologies.com

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    you're right on man, right on!

  9. #9
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    How about

    Though I never tested it , but objects created in Model, would they be created in tempdb when system restarts ? OR Tempdb doesn't copy model structure ?

  10. #10
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: How about

    Originally posted by aashu
    Though I never tested it , but objects created in Model, would they be created in tempdb when system restarts ? OR Tempdb doesn't copy model structure ?
    BOL:

    The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.

  11. #11
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Well

    If that is the case , Why not put all those users and permissions in Model !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •