How to change default database to all logins?

Question

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?

Answer 1

You should never try to update system tables directly, in most cases it is not going to let you as you have found.

In your case you will want to build out a dynamic statement for the ALTER LOGIN without knowing how many there are on the instance. However, you will need to be cautious in doing this to ensure you do not touch logins you shouldn't. This query should give you those logins that are "non-default" or installed via the installation process:

SELECT name
FROM sys.server_principals
WHERE type <> 'R'
    AND name NOT LIKE '##%' --Exclude policy based accounts
    AND name NOT LIKE 'NT%' --Exclude system accounts
    AND sid <> 0x01 --exclude the sa

From this you just need to build out your statement to execute via sp_executesql. You could use a simple cursor or while loop per your preference, below is a template for just a basic cursor that I keep handy:

DECLARE @v1 VARCHAR(50)

DECLARE v_cursor CURSOR FOR  
/* Query of data to iterate through */ 

OPEN v_cursor  
FETCH NEXT FROM v_cursor INTO @v1   

WHILE @@FETCH_STATUS = 0   
BEGIN   
    /* build your dynamic statement */
    /* Use @v1 within query to perform action on each row */
    FETCH NEXT FROM v_cursor INTO @v1   
END   

CLOSE v_cursor   
DEALLOCATE v_cursor;

Answer 2

Below is an example to generate the needed script.

DECLARE @SqlScript nvarchar(MAX);
SELECT @SqlScript = (SELECT N'ALTER LOGIN ' + QUOTENAME(name) + N' WITH DEFAULT_DATABASE = tempdb;'
FROM sys.server_principals
WHERE 
    default_database_name = 'master'
    AND type NOT IN('C', 'K')
FOR XML PATH(''));

EXEC sp_executesql @SqlScript;