I'm trying to use sp_ineachdb, please can anyone see where I'm getting it wrong?

Question

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

Answer 1

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.