how to prevent users from losing tempdb permissions

[mbfloyd]

Whenever I restart sql server my users lose access to the tempdb database. foxdev explained that I'm using this database as a default database. How do I not use this database as a default and continue to let my users have access to the database. Please help!!!

 

[Ruairi]

tempdb, as the name implies, is temporary. Nothing in tempdb is kept when the server is shut down. If you want to
assign permissions for users in the tempdb you should be able to do it in an automatic stored procedures. Just create a stored procedure which assigns the permissions (it must not have any input parameters and must be created by the SA) and assign it to run on startup. To do this use the
sp_makestartup system stored procedure.

sp_makestartup MyStoredProc

This stored procedure will run automatically whenever SQL server is started and should set your permissions. Ruairi
ruairi@logsoftware.com
Experienced with:

VB6, SQL Server, QBASIC, C(unix), MS Office VBA solutions

ALSO: Machine Control/Automation using GE and Omron PLC's and HMI(human machine interface) for industrial applications

 

[TomSark]

I would avoid this startup procedure business, rather, change the users login such that it uses another database as the default database other than tempdb as in:

For each login that you want to change execute this:
sp_defaultdb 'login name' , ' new default database'

You could easily automate this in a script using a cursor that retrieves the login name, and using this name, changes their default database. Tom Davis
tdavis@sark.com

http://www.sarkdallas.com