During the last years working with customers and teaching them in security topics, I realised that a lot of DBAs are not familiar with or aware of the differences between the two “owner” existing in SQL Server. That’s why I decided to write a blog about it.
Well, in SQL Server we are dealing with two principals in terms of “owning” a database:
- dbo
- db_owner
Principals in terms of SQL Server are entries that can request SQL Server resources.
We have so called server principals which are operating on instance-level – here we are talking about logins. At the other hand we have database principals which are operating at database-level – here we are talking about users.
While “dbo” is a single principal (a database-level user) who owns effectively the database, “db_owner” is a database role which can contain multiple users (the role itself is a principal as well as all their members) equipped with the rights to do any operation within the database – means executing DML (Data Manipulation Language – statements like SELECT, INSERT, UPDATE and DELETE) as well as DDL (Data Definition Language – statements like ALTER DATABASE etc.) and DCL (Data Control Language – statements like GRANT and REVOKE) against the database engine.
Furthermore every principal which is member of the server role “sysadmin” is not working under his own user in a database, he is mapped implicitly as “dbo” into each database.
There are tiny little differences between the two principals “dbo” and “db_owner” that I want to show you in several demos.
By the way: for my demo I’m using the new SSMS 21 which is available in preview these days. My colleague Stéphane Haby already written a blog post about the installation of the newest release of SSMS 21.
The range of functions and authorizations of the user “dbo” and members of the database role “db_owner” are quiet the same, but they differ in the following points:
- The “dbo” is able to restore his own database (WITH REPLACE) while members of the role “db_owner” are not
- All security checks are skippt when you acting as “dbo“, means DENYs are ignored meanwhile DENYs are taken into account when operating as a member of the role “db_owner“
Let’s dig into the demo: After creating the database “dbo_vs_dbowner” I’ve created two logins with descriptive names:

The login “Ced_dbo” is the owner of the database, the login “Ced_dbowner” is member of the database role db_owner:


The setup for the demo is now complete.
Now we come to the section, where I want to show you the different behaviour when working with each of them.
BACKUP/RESTORE
Let’s check if we have enough permissions to backup the database first.
Ced_dbo – of course, there is no problem to perform a backup of his own database:


Ced_dbowner – no problem to perform a backup of the database as well:


Now, let’s take a look at the behaviour when performing a restore.
Ced_dbo – Restore is working properly here:


Ced_dbowner – Performing a restore of the database is not possible under this credentials, because he is neither the owner nor member of the server role “db_creator” or “sysadmin”:


Caution: Performing a restore of his own database is only possible for the user Ced_dbo if he’s providing the parameter WITH REPLACE within the restore statement. If he drops his database first – yes this operation is allowed to execute for the “dbo” as well as for members of the database role “db_owner” – and tries to restore it afterwards, the operation will fail!
This makes sense because the master database keeps track of the owner of all databases. If a database is dropped, this information will be lost and SQL Server no longer has a clue who the owner of the database is/was and we need server level permissions (e.g. dbcreator or sysadmin) to create the database again from the backup.


Bypassing Security Checks
Now we are goinig to check out the different behaviour of the sessions with our 2 users mentioned above under this point.
Due to the fact that Microsoft also uses the principle of inheritance of authorisations in the SQL Server environment, DENY had to be added to the well-known GRANT and REVOKE control commands of a DBMS. This differs from other DBMS like for example Oracle. Generally speaking, within SQL Server we are confronted with the fact that a DENY is overriding every other permission set for a user or group.
SQL Server knows the concept of bypassing security checks when we are connected with users who are members of the server role “sysadmin” and owner of databases as well – means for all types of users like this, DENYs are completely ignored.
A few weeks ago I prepared myself to teach our SQL Server dbi-services course “DBA ESSENTIALS” and I was astonished to realise that things has changed obviously when working with SQL Server 2022. Security bypassing seemed to be skippt by Microsoft, because the database owner was unable to do stuff which was denied for public as well as all the other users, but…
…after performing countless tests on different versions of SQL Server 2022, with older versions of SSMS and so on and so forth I’m able to to confirm, that SQL Server 2022 behaves the same way as always and as expected. I haven’t got a clue what magic happens within my testing environment during my preparation 😊
Let’s dig into the next demo to illustrate and prove that things are behaving the same way the always did.
First of all, I created the Stored Procedure called “spr_getInfo” within the database as follows:
USE [dbo_vs_dbowner]
GO
CREATE PROCEDURE spr_getInfo
AS
BEGIN
SELECT * FROM [dbo_vs_dbowner].[dbo].[T1]
WHERE T1_ID = 3
END
Then I set a DENY to execute this Stored Procedure for all users:
USE [dbo_vs_dbowner]
GO
DENY EXECUTE ON OBJECT::dbo.spr_getInfo TO [public]
GO

Ced_dbowner – even this user is member of the database role “db_owner” which is granted to do everything within the database he is not allowed to execute the Stored Procedure because the DENY overrides the GRANT in terms of executing this Stored Procedure as mentioned above:


Selecting from the table directly is nevertheless allowed (same statement as used within the Stored Procedure), because the DENY impacts only the execution of the Stored Procedure but not the SELECT on the base table:

Now, let’s have a look how the execution behaves under the owner of the database.
Ced_dbo – as mentioned at the beginning of this section, for the owner of the database the DENY is ignored and a query result is being returned when the Stored Procedure is executed:

Conclusion
From my point of view, as a DBA it’s very important to know the tiny differences between “dbo” and members of the database role “db_owner” to avoid unpleasant surprises in our daily business.
And last but not least: things behave in the same manner as we are familiar with since a long time ago. Whether this is an advantage or a disadvantage remains a philosophical question…