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?
Difference Between User ‘dbo’ and Database Owner in SQL Server
Securitysql server
Related Question
- Sql-server – What’s the difference between EXECUTE AS USER and EXECUTE AS LOGIN that makes the second one behave unexpectedly
- SQL Server Permissions – How to Allow Developers to View Query Plan Without Running Trace
- SQL Server 2008 R2 – How to Deny DBO Schema Permission
- Sql-server – In the context of another principal what permissions hinge on the identity of the database owner
- Sql-server – Can changing database owner affect permissions or role memberships
Best Answer
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.databasesis 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 theowner_sidcolumn insys.databasesand the "dbo"sidinsys.database_principalsin that DB.As far as I am aware of, the record in
sys.database_principalsin each DB is the real owner, and theowner_sidcolumn insys.databasesis a matter of record-keeping / convenience (similar to denormalization; withoutsys.databasesthe 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 eitherEXTERNAL_ACCESSorUNSAFEwon't load if one has chosen to go the less secure route of enablingTRUSTWORTHYas that relies upon the "dbo" SID as it needs to match a Login that has either theEXTERNAL ACCESS ASSEMBLYorUNSAFE ASSEMBLYpermission. 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.