I'm trying to delete a user from the SSISDB data base that was apparently orphaned during a server upgrade. The Trigger ddl_cleanup_object_permissions stops any users from being dropped. I have already removed all of the privileges for the user I'm wanting to drop, but the trigger is still firing, so there must be some other orphans.
The query in the trigger that fires the error is:
SELECT [grantor_sid]
FROM [internal].[object_permissions]
WHERE [grantor_sid] NOT IN
(SELECT [sid] FROM [sys].[database_principals] where [sid] IS NOT NULL)
It returns several rows, but just one unique sid (this is not the sid for the user I'm trying to drop). If I look at the underlying tables for the view [internal].[object_permissions], all of the return rows are coming from [internal].[project_permissions].
My first question, if the sid from these records doesn't link to anything, can I safely delete these records?
If not, what is the best way to proceed?
I know this is a very old issue. I found similar questions going back many years, but it seems like the solutions were more specific. The queries that were suggested are not returning any results (typically they all have comparisons to the user I'm trying to delete, which I've already removed all references to). It looks like maybe the records are linked to a login that has been disabled, but I'm not fully convinced of that yet. Should sys.server_principals.sid = sys.database_principals.sid or are they independent?
I apologize if this or similar questions have been answered, I didn't see any search results that looked related. Any help would be greatly appreciated.