MS SQL: dbo versus db_owner

dbo vs. db_owner

In every database there is a special user, dbo. While dbo has the effective permissions of a database owner, it is not one and the same as the db_owner role. Any user account can be assigned to the db_owner role, giving that user complete control of the database. The dbo user account is a special account which all members of the sysadmin role are implicitly
mapped to.

Also, the system table sysdatabases (located in master) stores who is the owner of the database (and is assigned the dbo user account) in the sid field. So not only are sysadmins mapped to dbo, but so is the database owner. The dbo user will always have db_owner rights to a given database, but it is more than just a default user account.

The reason the sid of the database owner is important is in the case of a damaged database. Normally, a db_owner role member could restore a database, but the information on who belongs to the db_owner role is stored within the database itself. If the database is damaged, SQL Server won’t be able to determine who are members of the db_owner role.

The only logins it knows for certain have the appropriate rights is the db_owner (by virtue of the information stored in sysdatabases) and those who are assigned to the sysadmin fixed server role (since they have all rights anyway). Any time SQL Server cannot obtain the information about the db_owner database role because the database is unavailable, the actual owner (apart from the sysadmins) will be the only one who can perform such functions.

Another difference deals with permissions. If a user is a member of the db_owner role but not the dbo, DENY permissions still apply. In other words, if I issue a DENY to prevent a particular user from executing a stored procedure (or selecting data from a table, etc.), the user will be
unable to issue the execute statement (or SELECT, etc.) as SQL Server will prohibit the user from doing so. The dbo, however, will be able to carry out the command since the dbo naturally bypasses all permissions checks within the database.

Leave a Reply

Your email address will not be published. Required fields are marked *