1

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?

Background Info:

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.

Another approach:

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?

Improve this question
2
  • @JoshDarnell, Thank you. I have edited the question to add your attempt. On what version of SQL Server did you try it? One of the ideas that occurred to me was that newer versions of SQL Server could be more "user proof" than older ones and that's the reason we're having trouble to drop the principal.
    – Ronaldo
    Jul 14, 2020 at 14:27
  • I did that test on SQL Server 2016. That's an interesting theory about older versions! Could be some other wacky thing, like 1. set up a stoplist owned by a particular user, 2. uninstall FTS, 3. drop the user that owns the stoplist, 4. reinstall FTS. Maybe it keeps some metadata around or something silly like that. I don't have FTS installed, and haven't really used it, which is why I'm just leaving comments 😀
    – Josh Darnell
    Jul 14, 2020 at 15:04

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.