Sql-server – fulltext_stoplists to database_principals issue

While normal columns are stored in the actual row record (the structure on disk), LOB columns are stored on a different page, leaving a pointer to that page on the original page.

When you scan a table, all the data pages will be scanned, including the pointers to the LOB pages. If the timing is right, and you're running under READ UNCOMMITTED, you might scan the data page, but when the page containing the LOB data is scanned (by following the pointer in the referring record), it might have been changed/deleted in the meantime.

It could also be caused by corruption, in which case you should be able to reproduce the error again and again. In this case, run DBCC CHECKDB to check for corruption issues. If you can't reproduce it but it happens too often, see if you can't change the isolation level.

This appears to be a matter of access rights and the specific user in the database. The first thing to understand is that sys.databases in any database is simply a view that exposes parts of the system table that lives in resourcedb. Anytime you are querying that in any database, you're hitting the same table but through a different view.

The second thing to understand is each database has a dbo user. When you use the execute as user = N'dbo', that means you're dbo in the context of that specific database. In your specific example, the dbo user in master and the dbo user in tempdb are two different security principals.

What you're seeing is simply a matter of what each principal is allowed to see. dbo users, being the owner login of each specific database, only have rights to see the following databases:

• master (in order to look up control information)
• tempdb (in order to create temporary objects when necessary)
• The database they own

Note that the dbo user in master, since that database needs to see and supply control information for all databases, has rights to view all databases on the server.