I'm bit new to SQL Server and I'm trying to script out Microsoft SQL Server (2005-2014) post installation activities and I got bit stuck on altering logins. Corporate policy states that none login should have master database as default, thus my plan is to identify all logins that has master as default and change master to tempdb.
Single login can be altered using such query:
ALTER LOGIN [sa] WITH DEFAULT_DATABASE = tempdb
Trying to achieve this on all logins (without knowing their amount nor names), I know I was naive, when hoping something like this would work (but had to try):
UPDATE master..syslogins
SET dbname = 'tempdb'
WHERE dbname = 'master'
Obviously output was an error (logically):
Ad hoc updates to system catalogs are not allowed.
SQL Servers are in MIX authentication mode, so pretty much anything can be login - SQL login, Windows local or domain user or group (not sure if this can make any difference for solution). Solution should be pretty much version independent, but let's say starting SQL Server 2005 forward (...yes, still have some ancient version around...)
Can anyone please help me to find proper query to achieve this?