SQL Server 2000 tempDB user loosing permissions

[surfangel]

I am supporting a system that needs to allow users to have access to TempDB.

I set these users up using the GUI, but whenever the server is restarted, these users permissions are wiped out and the db_owner permission is lost and I have to manually go in and apply the permissons for the database to work again, it happens on most reboots but not all.

Is there anyway to keep these users permissions when the server is rebooted?

Your help is most appriciated.

P.S Could I create a stored procedure that when ever the Server is rebooted the procedure would recreate these permissions?

If I need to do this how would I go about doing this?

 

[onpnt]

Every time you reboot or restart SQL Server the tempDB is rebuilt. Yes, you could create a SP to execute the permissions but I think you should find an alternative to relying on the tempDB. Why do these users need access to it? Wouldn't it be more stable of a setup to create a tempDB or your own for them to perform whatever it is they need?

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[surfangel]

I wish it was that easy but I have no control over the system that accesses the database.

I know it has been setup wrong and they dont want to do it the right way. If it aint broke dont fix it. But I still need this database to work if and when the server reboots.

Could anyone help me with creating an SP? I have no real skills in this area.

Thank you

 

[Catadmin]

You don't need an SP. Just write a job with a T-SQL step that drops and re-adds the permissions in the DB.

Look up GRANT and sp_AddLogin in Books Online. This will give you more information on the code you need to use.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[surfangel]

Hi

I have tried the following to add the Database user to the tempdb in the query analyzer:

sp_addlogin rnlidbuser, its password, tempdb

but it comes up with rnlidbuser already exists, which it does. But I can manually add this user to tempdb and then give it permissions without any issues.

Am I using the wrong syntax?

Also what is the code to add permissions to this after I am sucessfull with the above statement?

Thanks

 

[surfangel]

Sorry I have just figured out how to add the user.

I did an sp_adduser username and it worked it added the user to tempdb.

Now i just need to find a command to add the db_owner permission for it.

Any idea what the stored procedure is for doing this?

 

[surfangel]

Right found a resolution

Created a script which basically done this:

sp_adduser username

then added a role to this user:

sp_addrolemember db_owner, username

Thanks for all your help guys