1

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'

6
  • What error message did you recieve? Mar 29, 2019 at 9:38
  • SELECT @cnt= p.name should be SELECT @cnt= COUNT(p.name) Mar 29, 2019 at 9:38
  • @DenisRubashkin, that's right. It should be COUNT(p.name). It was actually like that in the original script.
    – AiB
    Mar 29, 2019 at 9:43
  • @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'.
    – AiB
    Mar 29, 2019 at 9:51
  • You also have an excess END and infinite loop while @cnt > 0 and alter login p.name with default_database = 'master' won't work Mar 29, 2019 at 9:51

2 Answers 2

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
1
  • you are awesome. Thank you so much. It works perfect
    – AiB
    Mar 29, 2019 at 10:48
0
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.