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

sql serversystem-databasest-sql

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'

Best Answer

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
Related Question