Sql-server – Could not complete cursor operation because the set options have changed since the cursor was declared – SQL Server

cursorssql serversql-server-2016

I get this error when I execute this cursor.

Msg 16958, Level 16, State 3, Line 56 Could not complete cursor operation because the set options have changed since the cursor was declared.

But after that I review my login and his roles. Everything is fine. So I don't why i get this error but everything went fine.

USE MASTER
GO
  CREATE LOGIN [login1] with password = 'test12', 
  CHECK_POLICY=OFF;
GO

declare @ejecutable nvarchar(100)
declare @database   nvarchar(50)
declare @DynCommand nvarchar(max)

declare cr1_database cursor
local fast_forward
for
select name
from sys.server_principals
where name like 'login1';     

open cr1_database

fetch next from cr1_database into @ejecutable

WHILE (@@FETCH_STATUS = 0)

BEGIN
         SET @DynCommand =N'
        USE ? 
         IF (DB_NAME () NOT IN (''DBA''))           
        BEGIN
         create user [' + @ejecutable+ ']
         for login [' + @ejecutable+ '];
         exec sp_addrolemember ''db_datareader'', ['+@ejecutable+']
         exec sp_addrolemember ''db_datawriter'', ['+@ejecutable+']
         exec sp_addrolemember ''db_ddladmin'', ['+@ejecutable+']
         exec sp_addsrvrolemember ['+@ejecutable+'] , ''securityadmin''

        END '


    EXEC sp_foreachdb
    @command = @DynCommand, 
    @user_only = 1  

    BEGIN
        USE msdb
      BEGIN   
        CREATE user [login1] for login [login1]
        EXEC sp_addrolemember @rolename= 'SQLAgentReaderRole', @membername = 'login1'
        EXEC sp_addrolemember @rolename= 'db_datareader', @membername = 'login1'
      END   

    END

 FETCH NEXT FROM cr1_database INTO @ejecutable
END  
CLOSE cr1_database
DEALLOCATE cr1_database

Best Answer

@Tony You were correct that it is the use db option that messes with the set options (not sure why). But it is trivial to qualify the relevant queries for master and then run the core of the loop from msdb, negating the requirement to switch the database just to inject the user into that db.

   USE MASTER 
   GO
    CREATE LOGIN [login1] with password = 'test12', 
    CHECK_POLICY=OFF;
   GO

   USE MSDB
   GO

   declare @ejecutable nvarchar(100)
   declare @database   nvarchar(50)
   declare @DynCommand nvarchar(max)

   declare cr1_database cursor
   local fast_forward
   for
   select name
   from master.sys.server_principals
   where name like 'login1';     

   open cr1_database

   fetch next from cr1_database into @ejecutable

   WHILE (@@FETCH_STATUS = 0)

   BEGIN

          SET @DynCommand =N'
         USE ? 
          IF (DB_NAME () NOT IN (''DBA''))           
         BEGIN

          create user [' + @ejecutable+ ']
          for login [' + @ejecutable+ '];
          exec sp_addrolemember ''db_datareader'', ['+@ejecutable+']
          exec sp_addrolemember ''db_datawriter'', ['+@ejecutable+']
          exec sp_addrolemember ''db_ddladmin'', ['+@ejecutable+']
          exec sp_addsrvrolemember ['+@ejecutable+'] , ''securityadmin''

         END '


      EXEC master..sp_foreachdb
      @command = @DynCommand, 
      @user_only = 1  

      BEGIN  
        BEGIN   
         CREATE user [login1] for login [login1]
         EXEC sp_addrolemember @rolename= 'SQLAgentReaderRole', @membername = 'login1'
         EXEC sp_addrolemember @rolename= 'db_datareader', @membername = 'login1'
        END   

      END


    FETCH NEXT FROM cr1_database INTO @ejecutable
   END  
   CLOSE cr1_database
   DEALLOCATE cr1_database

Which adds the user to all the user dbs + msdb.

Related Question