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.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|||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.|||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.)|||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|||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.|||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|||you're right on man, right on!|||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 ?|||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.|||If that is the case , Why not put all those users and permissions in Model !!