SQL Server 2000 tempDB user losing permissions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server 2000 tempDB user losing permissions

Author

Topic

Kworn
Starting Member

7 Posts

Posted - 2007-01-15 : 09:42:53

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?

mr_mist
Grunnio

1870 Posts

Posted - 2007-01-15 : 10:27:08

TempDB is not really designed to be used like this. Why do they need direct access to tempdb?

If you really must add users, then it may be useful to know that tempdb is recreated based on the model database.

-------
Moo. :)

Kworn
Starting Member

7 Posts

Posted - 2007-01-15 : 11:15:00

I know I did not design the application that runs this.

It's a pian in the arse thats why it does not work properly.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-01-15 : 12:04:51

IF you can write a SP to automate the GRANTS, you can schedule this SP to execute on the SQL Scheduler.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-01-15 : 12:05:41

Alternatively....grant the apoplication access to TEMPDB...so that the permission is not specific to the users...

Kworn
Starting Member

7 Posts

Posted - 2007-01-15 : 13:11:14

Thanks Andrew but how do I do that?

Is there a whitepaper you could point me to, it's just I dont want to do more harm then good.

Kworn
Starting Member

7 Posts

Posted - 2007-01-17 : 08:14:11

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

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2007-01-17 : 08:31:37

sp_addlogin is to add a login.

u should use , sp_adduser rnlidbuser

and read about Login and User in BOL
madhu

Kworn
Starting Member

7 Posts

Posted - 2007-01-17 : 10:18:14

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources