How to check if sysadmin logins have a master database as a default database in T-SQL?

Question

I want to check whether all the sysadmin users in my sql server instance have a master database as their default database AND if not change their default database to master database. I tried the following, but it was not successful. Any help would be greatly appreciated.

DECLARE @cnt INT;
SELECT @cnt= COUNT(p.name)
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
AND p.name NOT LIKE '##%'
AND p.default_database_name not in ('master')
AND s.sysadmin = 1

IF (@cnt = 0) 
BEGIN
   exec master..xp_cmdshell 'echo '''all sysadmins have master database as their 
   default database> C:\test\result.txt'''
END 
ELSE
BEGIN
while @cnt > 0
BEGIN
    alter login p.name with default_database = 'master'     
END
END
END

The error message I get is

Msg 102, Level 15, State 1, Line 21 Incorrect syntax near '.'. Msg 102, Level 15, State 1, Line 24 Incorrect syntax near 'END'

Answer 1

You can get a list of such logins and then run alter login commands in a loop:

DECLARE @Name SYSNAME
DECLARE @Command NVARCHAR(2000)

DECLARE c CURSOR READ_ONLY
FOR
SELECT p.name
FROM sys.server_principals p
    JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
    AND p.name NOT LIKE '##%'
    AND p.default_database_name not in ('master')
    AND s.sysadmin = 1

OPEN c
FETCH NEXT FROM c INTO @Name

WHILE @@FETCH_STATUS = 0 BEGIN

    SET @Command = 'alter login ' + QUOTENAME(@Name) +' with default_database = [master]'
    --print @Command
    EXEC(@Command)
    FETCH NEXT FROM c INTO @Name

END

CLOSE c
DEALLOCATE c

Answer 2

DECLARE @QUERY NVARCHAR(1024)
DECLARE My_Cursor CURSOR FOR
select 'ALTER LOGIN '+name+' WITH DEFAULT_DATABASE=[master]' from 
sys.server_principals principals
inner join
                    (
                    select srm.*
                    from
                                sys.server_role_members srm
                    inner join  sys.server_principals sp
                    on          srm.role_principal_id=sp.principal_id
                    where
                                sp.name='sysadmin'
                    ) role_members
on principals.principal_id=role_members.member_principal_id
and principals.default_database_name!='master'
where principals.name not like '##%' and principals.name not like 'NT%'

OPEN My_Cursor
FETCH NEXT FROM My_Cursor INTO @QUERY
WHILE @@fetch_status=0
    BEGIN
    EXEC (@QUERY);
    FETCH NEXT FROM My_Cursor INTO @QUERY
    END
CLOSE My_Cursor
DEALLOCATE My_Cursor