ophaned user connecting

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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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?

Answer 8

Hi similar discussion here which may shed some light on this

Answer 9

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.