script to drop logins in a SQL Server instance throwing errors

All we need is an easy explanation of the problem, so here it is.

Please can anyone see what’s wrong in my code? I’m getting error:
Msg 3903, Level 16, State 1, Line 25
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

I’ve checked the syntax as much as I know. It works with the print statement but not with the drop login statement. The code is to drop all the logins in an instance expect the built-in ones.

Code pasted:

DECLARE @Login nvarchar(256);
DECLARE csrLogin CURSOR FOR
SELECT SP.name          
FROM sys.server_principals AS SP 
LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
        AND SP.name NOT LIKE '##%##'
        AND SP.name NOT LIKE 'NT AUTHORITY%'
        AND SP.name NOT LIKE 'NT SERVICE%'
        AND SP.name <> ('sa')
        AND SP.name <> 'distributor_admin';
        
        OPEN csrLogin;
FETCH NEXT FROM csrLogin INTO @Login;
WHILE @@FETCH_STATUS <> -1
BEGIN
    BEGIN TRY
    drop login [@Login]
    --print @Login
    END TRY
    BEGIN CATCH
      ROLLBACK
    END CATCH
    FETCH NEXT FROM csrLogin INTO @Login;
END;

CLOSE csrLogin;
DEALLOCATE csrLogin;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The reason for the rollback error is the script has no BEGIN TRANSACTION statement.

The reason the catch block was entered is because the DROP LOGIN statement is trying to drop a non-existing login named "@Login". DROP LOGIN does not accept a variable/expression for the login name.

TRY/CATCH and ROLLBACK are unnecessary because 1) the script will continue following an error and 2) each DROP LOGIN is in an atomic auto-commit transaction by default. Consequently, you can fix the problems by generating a single script with all the logins to be dropped and then executing the batch of statements in one go:

--SQL 2017 or later:
DECLARE @sql nvarchar(MAX);
SELECT @sql = STRING_AGG(N'DROP LOGIN ' + QUOTENAME(SP.name), N';')          
FROM sys.server_principals AS SP 
LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
        AND SP.name NOT LIKE '##%##'
        AND SP.name NOT LIKE 'NT AUTHORITY%'
        AND SP.name NOT LIKE 'NT SERVICE%'
        AND SP.name <> ('sa')
        AND SP.name <> 'distributor_admin';
PRINT @sql;
--EXEC sp_executesql @sql;
GO

--SQL 2016 and earlier:
DECLARE @sql nvarchar(MAX);
SELECT @sql = 
         STUFF((SELECT ';DROP LOGIN ' + + QUOTENAME(SP.name)
          FROM sys.server_principals AS SP 
LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
        AND SP.name NOT LIKE '##%##'
        AND SP.name NOT LIKE 'NT AUTHORITY%'
        AND SP.name NOT LIKE 'NT SERVICE%'
        AND SP.name <> ('sa')
        AND SP.name <> 'distributor_admin'
FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'),1,1,'') + N';'
PRINT @sql;
--EXEC sp_executesql @sql;
GO

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply