Can I connect to the database as the user "dbo"?

Question

If not, why?

During security audit we are being asked if the dbo login is interactive. My understanding is that the account is automatically created and managed by SQL server itself and is not directly interactive.

I have tried to the demonstrate that a sysadmin users would appear in SESSION_USER as dbo when authenticating but they're not fully convinced. Their question is now if it is possible to directly authenticate to dbo by logging in with its password.

Is this possible? I don't even know where I would find dbo's password if it has one.

Answer 1

The first part to understand is the difference between a User and a Login in SQL Server. This article provides a brief rundown to understand the difference, but to put it simply, a Login is a server-level principal that provides authentication and authorisation for server-level access. A User is a database-level principal that provides authorisation for database-level access.

There are many scenarios, but typically a Login is mapped to one or more Users, but only one User per database. A User is only ever mapped to one Login (except for contained database users, but that is a different scenario that can be ignored for now).

The dbo User is a built-in database user in every single database that represents the Database Owner. This user has full unrestricted access to the database. While you cannot login to a server as "dbo", you can login with a login that is mapped to the dbo user in one or more databases. When the database context of the connection is a database where that login is mapped to dbo, then the identity in that context is as "dbo".

In addition, as per comments, any login that has the sysadmin role will appear as dbo in every database as well, even if they're not explicitly set as the database owner.

To answer your specific questions:

During security audit we are being asked if the dbo login is interactive. My understanding is that the account is automatically created and managed by SQL server itself and is not directly interactive.

Yes, the dbo account is built-in and cannot be deleted, locked, disabled or removed. It also cannot be logged into directly, i.e. you cannot login as user "dbo" with a password.

I have tried to the demonstrate that a sysadmin users would appear in SESSION_USER as dbo when authenticating but they're not fully convinced. Their question is now if it is possible to directly authenticate to dbo by logging in with its password.

Is this possible? I don't even know where I would find dbo's password if it has one.

No, it is not possible. There is no "password" that exists for dbo. There may be a password for the mapped Login if it is a SQL Authentication login, but no direct login for dbo.

This article provides an explanation of dbo and also some useful queries for identifying your logins that are mapped to dbo in each DB. I would suggest you advise the auditors that it is not possible in SQL Server to login directly as dbo, nor can you adjust any permissions or disable/remove this account, then provide them the list of mappings and a list of sysadmins or logins with CONTROL SERVER permissions to illustrate who can assume the dbo user context in each database.

Answer 2

1st Point:

During security audit we are being asked if the dbo login is interactive.

---

dbo isn't a login, it's a user and checking the documentation you'll see that

Logins must be mapped to database user accounts in order to work with database objects.

If it's not a login, you can't provide it with a password to connect to a SQL Server instance and interact with it. You must provide a login with a password to do that.

Another characteristic of the dbo user is that by default it's a member of the fixed database role db_owner and according to Microsoft:

Fixed database roles have a pre-defined set of permissions that are designed to allow you to easily manage groups of permissions. Members of the db_owner role can perform all configuration and maintenance activities on the database.

As you can see the user dbo has quite a lot of privileges over a database.

The image bellow might help you explain the existance of the dbo user. It's from a brand new instalation of SQL Server. Notice that there is no dbo login among the ones in the Logins folder. Yet there is a dbo user on each of the system databases including model (what means there will be a dbo user on every database you create).

Observe that the login sa is mapped to the user dbo, so in this case you need to provide the login sa and its password on the connection screen to act as the user dbo on the database model of the picture.

The login that is related to the dbo user won't always be the sa login and it can be changed on user databases using the sp_changedbowner procedure.

2nd Point:

I have tried to the demonstrate that a sysadmin users would appear in SESSION_USER as dbo when authenticating but they're not fully convinced. Their question is now if it is possible to directly authenticate to dbo by logging in with its password.

---

You could use the documentation to enforce your argument as it states:

The dbo, or database owner, is a user account that has implied permissions to perform all activities in the database. Members of the sysadmin fixed server role are automatically mapped to dbo.

The function SESSION_USER might also return the user dbo as response for any login that was mapped to the user dbo as follows:

CREATE DATABASE MyNewDB;

CREATE LOGIN Ronaldo WITH PASSWORD = 'MySafePassWd123';

USE MyNewDB;

EXEC sp_changedbowner 'Ronaldo';

Now if I login as Ronaldo on the instance and open a session on the database MyNewDB, the function SESSION_USER returns:

I Hope it was helpful and complementary to HandyD's answer.