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'
SELECT @cnt= p.name
should beSELECT @cnt= COUNT(p.name)
END
and infinite loopwhile @cnt > 0
andalter login p.name with default_database = 'master'
won't work