Sample Header Ad - 728x90

Run TSQL alter db set trustworthy on multiple times

0 votes
1 answer
151 views
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
Asked by Affy (3 rep)
Apr 29, 2024, 02:34 PM
Last activity: Apr 29, 2024, 03:53 PM