Re: Master database must never be the default database for any login

Thanks for your answer, Erland.

I mentioned MODEL only as an example.
So, let's say that I want TEMPDB as my default database for all logins
created in SQL Server 2000.
Is it possible?

Thanks again,

Felipe

"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9AE2144842FYazorman@xxxxxxxxxxxx
Carlos Felipe França da Fonseca (carlosfelipefranca@xxxxxxxxx) writes:
By default, MASTER is the default database for any login created, unless
we change it.
Is it possible I configure SQL Server to set a default database other
than
MASTER to any logins created automatically?

You can change for existing logins, see my other post. You could handle
new logins with a server-wide DDL trigger if you are on SQL 2005.

For example, I would like to have all my logins pointing to MODEL
database
by default, unless we change it.

I understand the sentiment, and I agree that tempdb is a much better
choice
as a default database. Model, on the other hand is not. Generally, you
cannot create new databases, if there are active users in model. And the
problem with master as default database for a login with sysadmin privs,
is that he can create objects in master by mistake. Well, you don't want
objects created in model by mistake either.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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


.

Relevant Pages

  • Re: Problem with SQL Server 2005 Express Edition
    ... Use of included script samples are subject to the terms specified at ... One cause for that error is trying to use a SQL Server login on a database server that is configured to only accept Windows logins. ...
    (microsoft.public.sqlserver.security)
  • Re: Can I move tempdb?
    ... Tempdb file name changes take affect after SQL Server is restarted and are ... reference posted by details procedures for moving other database files. ... Model isn't used to determine database file locations. ...
    (microsoft.public.sqlserver.server)
  • Re: Checkpointing Not Happening in Simple Recovery Model
    ... You cannot set the recovery model in tempdb. ... Columnist, SQL Server Professional ... We would then have to issue an alter database ...
    (microsoft.public.sqlserver.server)
  • Re: db library error ?
    ... Tibor Karaszi, SQL Server MVP ... > the following error message apeared: ... > The log file for database 'tempdb' is full. ...
    (microsoft.public.sqlserver.server)
  • Re: Help Ive been an idiot
    ... You need to make sure that you readd any logins that were mapped to database ... Jasper Smith (SQL Server MVP) ... > connectionString, CommandType commandType, String commandText, ...
    (microsoft.public.sqlserver.setup)