0

We have a few SQL Server databases that needs to have trustworthy option set to 'ON' to allow for external assemblies to load, but every time there's any security patch, the option sets to off and the application breaks.

I don't want to create multiple SQL agent jobs to constantly run the set trustworthy on command, so I've been trying to develop a cursor that will run this command on the five databases.

This is what I have right now, but I'm an getting error saying that trustworthy is not a recognized SET option. Could somebody please look and help fix this?

Running on SQL Server 2019 Enterprise Edition.

Declare @db_name varchar (100)
Declare @command nvarchar (200)
Declare database_cursor cursor for
Select name from master.sys.sysdatabases
Where name in ('db1', 'db2', 'db3', 'db4', 'db5')

Open database_cursor

Fetch next from database_cursor into @db_name
While @@fetch_status = 0

Begin
     Select @command = 'ALTER DATABASE' + ''''+ @DB_NAME+ '''' + 'SET TRUSTWORTHY ON'
PRINT @COMMAND
EXEC SP_EXECUTESQL @COMMAND

FETCH NEXT FROM DATABASE_CURSOR INTO @DB_NAME
END

CLOSE DATABASE_CURSOR
DEALLOCATE DATABASE_CURSOR

1 Answer 1

3

Your command was building the string incorrectly. I've fixed the data type for database name as well.

DECLARE 
    @db_name sysname,
    @command nvarchar(200);

DECLARE database_cursor CURSOR 
FOR
SELECT
    name
FROM master.sys.sysdatabases
WHERE name IN
      (
          N'db1',
          N'db2',
          N'db3',
          N'db4',
          N'db5'
      );

OPEN database_cursor;

FETCH NEXT 
FROM database_cursor
INTO @db_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT
        @command = N'ALTER DATABASE ' + QUOTENAME(@db_name) + N' SET TRUSTWORTHY ON;';

    PRINT @command;

    EXEC sys.sp_executesql 
        @command;

    FETCH NEXT 
    FROM database_cursor
    INTO @db_name;
END;

CLOSE database_cursor;
DEALLOCATE database_cursor;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.