1

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.

  • And what could be the reason for the question to begin with? what risk are they trying to assess there? – ADyson Sep 4 at 23:54
  • Users are not the same as Logins. dba.stackexchange.com/a/160564 . See also docs.microsoft.com/en-us/sql/relational-databases/security/… . dbo is a database user and you can't log in as a database User, you can only log in with a server Login. Evidently your auditors don't have much knowledge of SQL Server, despite apparently asking very detailed questions about your instance of it. Would they even understand the answer? – ADyson Sep 4 at 23:56
  • 1
    You may not have a full understanding yourself, that's fine. You're not the one in a position of power and authority asking detailed technical questions about a topic you clearly don't understand. A little knowledge is a dangerous thing...they seem to know enough to know that dbo exists, but little or no idea about what it actually is or how it works. IMHO someone who wants to poke their nose that deep into the fine details of the security of your database and challenge it really ought to know the answer to this kind of question already. Otherwise how are they going to assess your answers? – ADyson Sep 5 at 0:08
  • 1
    I'm formally a helpdesk member so SQL server isn't my specialty. For them, I think they're basing their questions on a standard list of questions or something. He's a pretty young guy so I'm sympathetic to his struggles. Like I imagine once he gets this, he's going to think "oh...". I think for both of us it's learn as you go type of deal. Thanks for your help! – neiltevetts101 Sep 5 at 0:17
  • 3
    Surely an auditor with experience and knowledge in these matters should be talking directly to your DBA. Good luck in your task, at least you both might learn something good and useful. – ADyson Sep 5 at 0:20
8
+500

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.

  • 1
    One addition. Any login which is a member of the sysadmin fixed server role connects to every databases as dbo without being explicitly mapped to the dbo user. – David Browne - Microsoft Sep 5 at 2:30
  • @DavidBrowne-Microsoft That's true, I've updated the answer. I believe any user with explicit CONTROL SERVER permissions appears as dbo as well, can you confirm? – HandyD Sep 5 at 3:34
1

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).

sa login bound to user dbo

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:

Ronaldo dbo user

I Hope it was helpful and complementary to HandyD's 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.