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.
| Author |
Topic |
|
all4csuns
Starting Member
2 Posts |
 Posted - 2004-04-06 : 10:25:17
|
| Using: SQL Server 2000 on Win2k server and Windows Authentication (Active Directory)Every time the server is rebooted, all group/user rights to tempdb are lost and we have to re-add them manually. I thought even the guest account automatically had rights to tempdb...? I never had this problem before, but I had always used SQL logins for database access. I've searched and not found anything about tempdb user rights "vanishing". Any help would be appreciated. |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2004-04-06 : 10:36:37
|
Are you sure you have searched ??Check BOL on Topic 'System Databases and Dataquote:
BOL Says : tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another. tempdb autogrows as needed. Each time the system is started, tempdb is reset to its default size. You can avoid the overhead of having tempdb autogrow by using ALTER DATABASE to increase the size of tempdb.
- Sekar |
 |
|
|
all4csuns
Starting Member
2 Posts |
Posted - 2004-04-06 : 12:07:50
|
quote:
Originally posted by samsekar
Are you sure you have searched ??Check BOL on Topic 'System Databases and Dataquote:
BOL Says : tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another. tempdb autogrows as needed. Each time the system is started, tempdb is reset to its default size. You can avoid the overhead of having tempdb autogrow by using ALTER DATABASE to increase the size of tempdb.
- Sekar
Color me blond, but I don't understand what your quote has to do with users under Windows Authentication needing specific rights granted to tempdb whereas SQL Logins - even the guest account - automatically have those rights. I know that tempdb is blown away and recreated with every reboot. Are you implying that with Windows Auth groups/logins that this is SUPPOSED to happen? Thanx  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-06 : 12:27:12
|
| Why do users need rights to tempdb?Perhaps you can grant the users the same access in the model database.Tara |
|
|
|
|
|
|
tempdb user rights