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

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: unable to start SQL Service ... error code 3417
    ... How about just create a new instance and restore databases to the new instance? ... I wouldn't go with restoring master from another instance. ... The supported route is to rebuild your master database, start SQL Server in single user mode, ...
    (microsoft.public.sqlserver.server)
  • 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: 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)
  • Re: Master database must never be the default database for any login
    ... let's say that I want TEMPDB as my default database for all logins ... Is it possible I configure SQL Server to set a default database other ... MASTER to any logins created automatically? ...
    (microsoft.public.sqlserver.security)
  • Re: Managing SQL database rights using AD
    ... logins to SQL Server. ... You would add these two logins you ... just added as users in the database. ... the Windows group that you just added as logins (and then ...
    (microsoft.public.sqlserver.security)