2

Can anyone help please? I can't seem to find where I'm missing it. I'm trying to loop through the user databases in my SQL Server instance to delete all the existing users. I'm using the stored proc dbo.sp_ineachdb. For each database, I get an error that the cursor already exists. My code is pasted below. Thank you.

DECLARE @sql AS VARCHAR(4000)
SET @sql = 

'USE [' + DB_NAME() +']
DECLARE @UserName nvarchar(256)
DECLARE csrUser CURSOR FOR
SELECT [name] FROM sys.database_principals WHERE principal_id > 4 AND is_fixed_role < 1 ORDER BY [name]

OPEN csrUser FETCH NEXT FROM csrUser INTO @UserName WHILE @@FETCH_STATUS <> -1
BEGIN
    BEGIN TRY
      EXEC sp_revokedbaccess @UserName
    END TRY
    BEGIN CATCH
      ROLLBACK
    END CATCH
FETCH NEXT FROM csrUser INTO @UserName
END

CLOSE csrUser DEALLOCATE csrUser'

EXEC dbo.sp_ineachdb @command = @sql

1 Answer 1

4

Construct the @sql variable like this:

SET @sql = 

N'DECLARE @UserName nvarchar(256);
DECLARE csrUser CURSOR FOR
SELECT [name] 
FROM sys.database_principals 
WHERE principal_id > 4 
    AND is_fixed_role < 1 
ORDER BY [name];

OPEN csrUser;
FETCH NEXT FROM csrUser INTO @UserName;
WHILE @@FETCH_STATUS <> -1
BEGIN
    BEGIN TRY
      EXEC sp_revokedbaccess @UserName;
    END TRY
    BEGIN CATCH
      ROLLBACK
    END CATCH
    FETCH NEXT FROM csrUser INTO @UserName;
END

CLOSE csrUser;
DEALLOCATE csrUser;';

The first line, with USE [' + DB_NAME() + '] is entirely unnecessary since sp_ineachdb will execute the statement inside the context of each database automatically.

As a side-note, notice that I've put each statement on it's own line, and terminated each statement with a semi-colon. This makes for better reading, and is easier to debug.

2
  • And terminators are a best practice too!
    – SMor
    Jun 29, 2021 at 21:24
  • 1
    Also, Microsoft has announced non-semicolon-terminated statements will be deprecated in a future version, so there is that.
    – Hannah Vernon
    Jun 29, 2021 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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