Using tempdb as a users default database

[Martin]

Hi,

I would like to ask what people think of using Tembdb as a users
default database.

I ask this because, when the instance is re-started tempdb is
recreated and any user accounts in there are lost,
however, i beleive, that any user gets access to tempdb and master by
default.

The reason I make all my users have tempdb as their default database
is that I used to set it to master (or some other database) and often
they would simply log in with SSMS and run create scripts straight
away and the the wrong database would have all the wrong objects - you
should see some of the master db's on my dev server :)

thanks for sharing your opinions.

cheers

martin.

[Sylvain Lafontaine]

This suggestion has the potential of making people loosing some of their
work. Why don't you create a dedicated database just for that instead?
(And possibly even a separate dedicated database for each people).

--
Sylvain Lafontaine, ing.
MVP - Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"Martin" <martyca...@gmail.com> wrote in message
news:8ca25346-e6ca-4440...@s24g2000pri.googlegroups.com...

[Martin]

Hi Sylvian,

I am not sure that I understand your comment "This suggestion has the

potential of making people loosing some of their work."

The only way I can see this happening is if the user was working in
tempdb (say creating tables, sps ect) and tempdb were recycled for
some reason - say a server root. The they would loose all of their
work.

I think I'd prefer that option to having then create hundreds of
objects in master by mistake.

if you could expand a little on your answer then that would be good.

cheers

martin.

On Aug 31, 1:28 pm, "Sylvain Lafontaine"

<sylvainlafontaine2...@yahoo.ca> wrote:
> This suggestion has the potential of making people loosing some of their
> work.  Why don't you create a dedicated database just for that instead?
> (And possibly even a separate dedicated database for each people).
>
> --
> Sylvain Lafontaine, ing.
> MVP - Access
> Blog/web site:http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>

> "Martin" <martycather...@gmail.com> wrote in message

>
> news:8ca25346-e6ca-4440...@s24g2000pri.googlegroups.com...
>
>
>
> > Hi,
>
> > I would like to ask what people think of using Tembdb as a users
> > default database.
>
> > I ask this because, when the instance is re-started tempdb is
> > recreated and any user accounts in there are lost,
> > however, i beleive, that any user gets access to tempdb and master by
> > default.
>
> > The reason I make all my users have tempdb as their default database
> > is that I used to set it to master (or some other database) and often
> > they would simply log in with SSMS and run create scripts straight
> > away and the the wrong database would have all the wrong objects - you
> > should see some of the master db's on my dev server :)
>
> > thanks for sharing your opinions.
>
> > cheers
>

> > martin.- Hide quoted text -
>
> - Show quoted text -

[Erland Sommarskog]

Well, if these users have permission to create tables in master, this means
that they are sysadmin or something similar. Which may be OK on a
development server.

I think setting tempdb as the default DB for these users is a good idea.
Better that they lose their work, than littering master. The point is that
developers should not rely on "saving" any work in a database - scripts
should be kept under version control.

If these users are not developers, they should not have rights to create
tables at master at all, and that is what you should address.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Sylvain Lafontaine]

What you want is not to clutter the master database with unwelcome object
(tables, SP, etc.) but the tempdb can be recycled. I agree that the
possibility of having the tempdb beeing recycled is low; however, it's not
null and people might have a tendancy of keeping some things for a long
time; especially when it comes to testing and debugging objects.

For example, myself, I could write some quick script in order to test for
something and it's quite possible that I might leave it aside for an
extended period of time; kind of many days, weeks or even months before I
use it again. So, using the tempdb has definitely the potential of making
people lose some useful work.

By using a dedicated database, you can be protected from the accidental
cluttering of the master database while also escaping the possibility of
losing some work because the tempdb would have been recycled in the
meantime. If you don't have to many developers, using a different database
for each of them would be the optimum in term of safeguard and privacy.

--
Sylvain Lafontaine, ing.
MVP - Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"Martin" <martyca...@gmail.com> wrote in message

news:49d2e4aa-92cc-4430...@n19g2000prf.googlegroups.com...