none
How should I know the mapping between a database user and its login

    Question

  • Hi everyone,

    I'm using a SQL server 2005. I have a user in a database, for example, "Domain\Name1". However, I'm not able to find the corresponding login name on instance level(I searched security folder, but no luck). According to my understanding, a database user should be mapping to a login. Could you please let me know how should I check a database user's login name? Thanks a lot.

    regards,

    Oliver

    Thursday, February 07, 2013 12:36 PM

Answers

  • You might find it useful to execute this:

    EXECUTE AS USER = '<username>';
    SELECT USER, SUSER_SNAME();
    REVERT;

    Consider also, a user might have access because the user is a member of a Windows Group, and the Windows Group has a login. And if the user is a member of two Windows Groups that have logins, the user can connect, but they have rights aggregated from both logins. It can get confusing. And might never have a login under their own Windows User name. But can still have a database user account.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Oliver_Wang Thursday, February 07, 2013 5:20 PM
    Thursday, February 07, 2013 4:15 PM

All replies

  • A server login can be placed in a group, such as the system administrator, that will enable that group to access a database.  This means that multiple users can belong to a group that will map back to a single login that has access to a database.  There is no real one to one relationship between server logins and database users.  You can query the catalog views to see the server roles and logins that map to database logins,

    SELECT dp.name,
           sp.name,
           dp.type_desc,
           sp.type_desc
    FROM sys.database_principals dp JOIN sys.server_principals sp
    ON dp.principal_id = sp.principal_id


    David Dye My Blog

    Thursday, February 07, 2013 12:54 PM
  • Hi David,

    Thanks for you reply. I tried your scripts and it returned me logins and corresponding users and it is a one-one relation. Actually I just want to do it in a reverse way, that is, from a database user, we find its corresponding login. 

    regards,

    Oliver

    Thursday, February 07, 2013 1:49 PM
  • The following query will match the server based login to the database user based upon the security identifier.  This will map the login directly to the user, but DOES NOT show the server roles that contain users:

    SELECT dp.name AS databsaeuser,
           sp.name AS login,
           dp.type_desc AS dbuser_type,
           sp.type_desc AS login_type
    FROM sys.database_principals dp JOIN sys.server_principals sp
    ON dp.sid = sp.sid


    David Dye My Blog

    Thursday, February 07, 2013 1:56 PM
  • Oliver,

    In addition to what David has posted, a little background on users and logins.

    1 login can map to many users

    1 user can only map to one login

    In your case you are asking from a database user, what is the login. What David had is correct because a user can only be mapped to one login.

    -Sean


    Sean Gallardy | Blog | Twitter

    Thursday, February 07, 2013 2:26 PM
  • Hi Sean,

    Thanks for your reply. Actually the situation here is I have 100 users in one database and 10 logins in the instance, and I want to figure out which users are mapped to which login. However, for some users, I couldn't find their corresponding logins. 

    regards,

    Oliver

    Thursday, February 07, 2013 2:41 PM
  • Oliver,

    They might be orphaned users. What happens when you run the following? Do those users show up in the output?

    exec sp_change_users_login 'report'

    -Sean


    Sean Gallardy | Blog | Twitter

    Thursday, February 07, 2013 2:44 PM
  • Hi Sean,

    I tried your scripts and it returned nothing.

    regards,

    Oliver

    Thursday, February 07, 2013 2:57 PM
  • You might find it useful to execute this:

    EXECUTE AS USER = '<username>';
    SELECT USER, SUSER_SNAME();
    REVERT;

    Consider also, a user might have access because the user is a member of a Windows Group, and the Windows Group has a login. And if the user is a member of two Windows Groups that have logins, the user can connect, but they have rights aggregated from both logins. It can get confusing. And might never have a login under their own Windows User name. But can still have a database user account.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Oliver_Wang Thursday, February 07, 2013 5:20 PM
    Thursday, February 07, 2013 4:15 PM
  • Oliver,

    I'm assuming you ran it in the context of the correct database? In addition to what Rick has ponted out, it may be that they really do not have a valid login. If you take David's query and check for only the ones that don't have a valid login:

    SELECT dp.name AS databsaeuser,
           sp.name AS login,
           dp.type_desc AS dbuser_type,
           sp.type_desc AS login_type
    FROM sys.database_principals dp left outer JOIN sys.server_principals sp
    ON dp.sid = sp.sid
    WHERE 
    	sp.name is null
    	and dp.type_desc != 'DATABASE_ROLE'
    	and dp.name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')

    -Sean


    Sean Gallardy | Blog | Twitter

    Thursday, February 07, 2013 4:17 PM
  • Hi Rick, Sean,

    Thank you very much for your reply. I think your explanation is the crux and very helpful. I even tried adding a domain acount as an user in a database, and I found as long as the domain account is valid, it could be added successfully in the database even before the corresponding login using the same domain account was created. So adding a user does not necessarily require a explicit login. Thanks again for your time and efforts~~~~~

    regards,

    Oliver

    Thursday, February 07, 2013 5:20 PM