0

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
  • Msg 16958, Level 16, State 3, Line 56 Could not complete cursor operation because the set options have changed since the cursor was declared. – SakZepelin Nov 7 at 9:20
  • Maybe because of the "USE mdsb" statement. See last post at social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Tony Hinkle Nov 7 at 10:01
  • Probably, i need check this. But i must do it in only one script Anyways. Thanks for you help and your time – SakZepelin Nov 7 at 10:41
  • You could get rid of the cursor by inserting the login names to a temp table, and then in a while loop, select top 1, process, delete top 1. Or, you could create stored procedures with the commands you need to run and call them from this script. – Tony Hinkle Nov 7 at 15:04
2

@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.

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.