none
ophaned user connecting RRS feed

  • Question

  • I have this db_owner user for which the login was erased.

    I find no corresponding login via the system tables

    SELECT dp.type_desc, dp.SID, dp.name AS user_name  , sp.name as loginame
    FROM sys.database_principals AS dp  
    LEFT JOIN sys.server_principals AS sp  
        ON dp.SID = sp.SID  
    WHERE sp.SID IS NULL  

    no privileges granted are via groups

    xp_logininfo 'mydomain\myuser', 'all'

    The database is not self contained.

    Yet my user successfully logs in and accesses the table.  The user is also not reported when using "sp_change_users_login @action = 'Report'"

    I am a bit at a loss as to why the user is still able to access the database.  version is SQL server 2012.

    Wednesday, January 18, 2017 7:47 PM

All replies

  • Hi, sp_change_users_login only works for SQL logins not Windows logins.

    xp_logininfo struggles with groups in groups so you may find that this login is a member of group which is in a group which has access, if that makes sense.

    Wednesday, January 18, 2017 8:43 PM
  • Have you tried these to track down users/logins?

    • sp_helpuser
    • sp_helplogins

    I use these stored procs to identify orphaned users/logins and keep them cleaned out on my servers:

    • usp_OrphanLogins
    • usp_remove_orphan_users
    • Scripts


    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Wednesday, January 18, 2017 9:22 PM
    Wednesday, January 18, 2017 9:02 PM
  • Have you tried these to track down users/logins?

    • sp_helpuser
    • sp_helplogins

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Thanks,

    sp_helpuser for the user return null loginame.

    sp_helplogins returns nothing.

    Wednesday, January 18, 2017 9:09 PM
  • Hi, sp_change_users_login only works for SQL logins not Windows logins.

    xp_logininfo struggles with groups in groups so you may find that this login is a member of group which is in a group which has access, if that makes sense.

    Thanks for the clarification regarding sp_change_users_login.

    I have some nested group security on the server but this specific database does not have users corresponding to groups. I should have skipped this test...

    Wednesday, January 18, 2017 9:18 PM
  • Is there some sort of memory/buffer for logins.

    I removed the user and I can confirm this was the only element granting accesses to the database.  how can a user continue working after the corresponding login is erased.

    Friday, January 20, 2017 3:54 PM
  • What you may have found is the user connected via left over open session and by passing the login process. Once the user was finally removed the session was removed.
    Friday, January 20, 2017 6:47 PM
  • I impersonatted the user and successfully connected.

    would lingering connection have an impact.  i.e. can I reuse a previous connection if my user does not have a corresponding login?

    Monday, February 13, 2017 8:14 PM
  • Hi similar discussion here which may shed some light on this
    Monday, February 13, 2017 8:28 PM
  • Thanks Kevin, the discussion you posted refers to users created without login which are used to perform impersonification.  It does not correspond to my case.  I have an orphaned user with a defined login which does not exists anymore.

    It is as if the login is still in cache even though he has no correspondance.

    I erased the user so I do not have the issue anymore.  I do wonder however why sometime I have to restart SMSQL to have permission kick in (AD) or in this case why removing the login did not remove the access. 

    Tuesday, February 14, 2017 10:33 PM