Given that SQL Server blocks me when I try to
DROP a principal that owns an object in the database, if I see an object which the owner no longer exists, should I assume right away it was data corruption or is there any other way a database principal can disappear leaving behind an object it owned?
Working to solve this question related to an orphaned fulltext_stoplist I failed to reproduce the problem.
All my attempts to
DROP the principal (user, role or application role) that owned the fulltext_stoplist threw the following error:
Msg 15138, Level 16, State 1, Line 7
The database principal owns a fulltext stoplist in the database, and cannot be dropped.
When I tried to
DELETE FROM sys.database_principals, I got this error:
Msg 259, Level 16, State 1, Line 15
Ad hoc updates to system catalogs are not allowed.
I tried connecting through the dedicated admin connection (DAC) and deleting from the underlying system table (
[sys].[sysowners]), and got the same error. (Thanks, Josh Darnell)
So, is there any reckless or forceful (and reproducible) way to drop a database principal leaving an orphaned object or, if I ever see such a situation, I can assume the cause was data corruption with no further investigation?