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'

• George.Palacios: What error message did you recieve?
• Denis Rubashkin: SELECT @cnt= p.name should be SELECT @cnt= COUNT(p.name)
• AiB: @DenisRubashkin, that's right. It should be COUNT(p.name). It was actually like that in the original script.
• AiB: @George.Palacios, Here is the error Msg 102, Level 15, State 1, Line 21 Incorrect syntax near '.'. Msg 102, Level 15, State 1, Line 24 Incorrect syntax near 'END'.
• Denis Rubashkin: You also have an excess END and infinite loop while @cnt > 0 and alter login p.name with default_database = 'master' won't work

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

• AiB: you are awesome. Thank you so much. It works perfect

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