Search code examples
sqlsql-serverprincipal

Why login mapping on 'dbo'

In database Design the user of 'fed' compared with login 'fed'.

USE [Design]
SELECT l.name AS login_name, p.name AS [user_name]
FROM sys.sql_logins l JOIN sys.database_principals p ON l.SID = p.SID
WHERE l.name = 'fed'

enter image description here

Why the request under login of 'fed' on the Design database select 'dbo'

USE [Design]
SELECT USER AS user_name

enter image description here

Solution

  • Read this first: CREATE USER

    You are confusing LOGIN with USER. Your first query is inspecting LOGIN information, whereas the second is looking at USER. Multiple LOGINs can map to a single USER, which makes things easier to manage, such as administrators all mapped to the database-owner user 'dbo'.

    EDIT Just to clarify, just because you named the column as user_name p.name AS [user_name] doesn't mean it is. You're linking via sys.sql_logins, so the 'principal' that you are retrieving is the login object, who's name property is obviously the login name, not the user.