11

We recently had a question come up where the user dbo in a database had an sid that didn't match the owner_sid in sys.databases. I understand how the owner of the database is different from members of the role db_owner but I had always thought the user dbo was the actual owner of the database. Is that not the case? And if so are there any real differences between dbo and what's in sys.databases?

8

I had always thought the user dbo was the actual owner of the database.

That is (or at least should be) correct. The name "dbo" of that User never changes, yet the underlying SID does depending on who created the database, or who it was set to be via sp_changedbowner (up though, and including, SQL Server 2005) or ALTER AUTHORIZATION (starting with SQL Server 2008).

In all three of those cases, the record in sys.databases is also changed so that they are kept in sync. However, when restoring a Database, either from another system or from the same Instance but from a DB that was backed-up / detached prior to one of those 2 SQL commands being executed to change the owner, then upon RESTORE or attach, there will be a mismatch between the owner_sid column in sys.databases and the "dbo" sid in sys.database_principals in that DB.

As far as I am aware of, the record in sys.database_principals in each DB is the real owner, and the owner_sid column in sys.databases is a matter of record-keeping / convenience (similar to denormalization; without sys.databases the system would need to make separate queries across all DBs to get that info, each time requested!) and security. One thing it is used for is to identify a potentially harmful / invalid restored / attached DB is those records do not match. Attempting to access SQLCLR Assemblies marked as either EXTERNAL_ACCESS or UNSAFE won't load if one has chosen to go the less secure route of enabling TRUSTWORTHY as that relies upon the "dbo" SID as it needs to match a Login that has either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission. And when there is a mismatch in the SID between those two system catalog views, it can't be determined which one to use, and used as a red-flag for there being a potential security issue. In fact, I test for this condition in the installation script for SQL# to alert someone to make the appropriate change, just so that they don't have to waste time hunting it down in case SQL Server complains about it at some point.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.