Modify default database of all logins   

I need to modify the default database of all logins.
Is there a way to do it using Transact-SQL?

Thanks,

Felipe

date: Sun, 20 Jul 2008 18:01:29 -0300   author:   Carlos Felipe Fran?a da Fonseca

Re: Modify default database of all logins   

Carlos Felipe França da Fonseca (carlosfelipefranca@gmail.com) writes:
> I need to modify the default database of all logins.
> Is there a way to do it using Transact-SQL?
 
If you are on SQL 2005:

   SELECT 'ALTER LOGIN ' + quotename(name) +  
          ' WITH DEFAULT_DATABASE = thedbofyourdreams'
   FROM  sys.server_principals
   WHERE type IN ('U', 'S', 'G')
     AND name <> 'sa'
     AND name <> 'BUILTIN\Administrators'

Copy result into a query window and run.

On SQL 2000:

   SELECT 'EXEC sp_defaultdb ' + quotename(name) + ', 'thedbofyourchocie'
   FROM   syslogins
   WHERE  name NOT IN ('BUILTIN\Administrators', 'sa')


-- 
Erland Sommarskog, SQL Server MVP, esquel@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

date: Sun, 20 Jul 2008 14:57:03 -0700   author:   Erland Sommarskog