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

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

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

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

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.

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