Question :
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
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.