locked
Identify Windows database user created without login RRS feed

  • Question

  • Hi guys,

    I'm running into an issue when try to sweep out orphaned database users. Basically, it's about the way how to identify orphaned Windows database users from Windows database users that are created without login. For example, I create two Windows logins as below. [DOMAIN1\user1] is a Windows domain accout, [DOMAIN1\group1] is a Windows domain group, and [DOMAIN1\user1] is a member of [DOMAIN1\group1].

    USE master 
    GO
    
    CREATE LOGIN [DOMAIN1\user1] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
    GO
    
    CREATE LOGIN [DOMAIN1\group1] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
    GO


    Under a user database, I create two database users as below. [DOMAIN1\jack] is also a Windows domain account, but NOT a member of  [DOMAIN1\group1].

    USE db_user
    GO
    
    CREATE USER [DOMAIN1\user1] WITH LOGIN [DOMAIN1\user1]
    GO
    
    CREATE USER [DOMAIN1\jack] WITHOUT LOGIN
    GO

    For some reason, We drop the Windows login [DOMAIN1\user1]. In this case, user - [DOMAIN1\user1] on db_user database is orphaned database user. I'd like to pick up this type of orphaned Windows users for dropping.

    Because SIDs for both [DOMAIN1\user1] and [DOMAIN1\jack] are not existing on sys.server_principals, and the length of SID on sys.database_principals is 28 for all Windows type users, I cannot find a way to identify which one is a valid user without login, and which one is invalid orphaned user.

    Any thoughts?

    Thanks in advance.


    Monday, June 17, 2013 8:26 AM

All replies

  • Hello Stephanie,

    query all database users of type U + outer join on server principals, to get all db user without an login:

    SELECT DP.*
    FROM sys.database_principals AS DP
         LEFT JOIN
         sys.server_principals AS SP
             ON DP.sid = SP.sid
    WHERE DP.type = 'U'
          AND SP.sid IS NULL


    Olaf Helper

    Blog Xing

    Monday, June 17, 2013 9:02 AM
  • Thanks for quick response Olaf!

    I don't just want to get database users without login, but also want identify database users without login from orphaned database users. I need to drop orphaned users but leave valid users created without login.



    Monday, June 17, 2013 9:59 AM
  • From Raul Garcia's blog post at:

    http://blogs.msdn.com/b/raulga/archive/2006/07/03/655587.aspx

    "Pretty much the only way to distinguish between a user and a user without login is by looking at the SID. Regular SQL users will have a GUID as a sid (16 bytes) while users without login will have a SID structure (28 bytes for current version of SQL Server). "

    A quick note: A loginless user cannot have a / in the name.

    Raul has a posted a response in 2011 with code that distinguishes the loginless user from an orphaned user.  As follows:

    -- Finding users without login SELECT * FROM sys.database_principals WHERE Datalength(sid) > 16 -- users mapped to logins have a 16 bytes SID, -- users without login have a length of ~28 bytes AND sid NOT IN (SELECT sid FROM sys.server_principals) -- No login with a matching SID AND type = 'S' -- Only SQL users AND principal_id > 4 -- filter system (well-known) principals go -- Finding orphaned users SELECT * FROM sys.database_principals WHERE Datalength(sid) <= 16 -- users mapped to logins have a 16 bytes SID, -- users without login have a length of ~28 bytes AND sid NOT IN (SELECT sid FROM sys.server_principals) -- No login with a matching SID AND type = 'S' -- Only SQL users AND principal_id > 4 -- filter system (well-known) principals go

    All the best,

    RLF


    • Edited by SQLWork Monday, June 17, 2013 5:40 PM
    Monday, June 17, 2013 5:39 PM
  • Thanks Russell.

    "Pretty much the only way to distinguish between a user and a user without login is by looking at the SID. Regular SQL users will have a GUID as a sid (16 bytes) while users without login will have a SID structure (28 bytes for current version of SQL Server). "

    But this is applied for SQL user (type='S') only, not for Windows users. As you can see from sys.database_principals DMV, for Windows user (type = 'U'), all SIDs' length is 28 bytes, whenever with or without login.


    Tuesday, June 18, 2013 2:26 AM
  • I was trying to capture the error by issuing  "EXECUTE AS USER = [DOMAIN1\user1]" and "EXECUTE AS USER = [DOMAIN1\jack]". For an orphaned user - [DOMAIN1\user1], it should raise an error because the login -[DOMAIN1\user1] has been dropped; For user without login - [DOMAIN1\jack], this should work.

    However, we have a Windows Group login - 
    [DOMAIN1\group1]. As [DOMAIN1\user1] is a member in this Windows Group,  "EXECUTE AS USER = [DOMAIN1\user1]" still works. 

    This makes me frustrated :(


    Tuesday, June 18, 2013 2:34 AM
  • Stephanie,  OK, I think that I understand better.

    As I understand it, creating a user without a login only works for user type 'S', not user type 'U'.  This is, of course, because there is no login. And, in that case, the trick mentioned above would allow you to see a user created WITHOUT LOGIN.

    However, I now understand your case better.  You have a user that does have a login, but the login is not on the server.  That is because the user's login comes in indirectly, through the nested Windows groups.  A couple of tools to help:

    exec xp_logininfo 'domain\login', 'all'

    exec sp_helplogins 'domain\login'

    Using xp_logininfo with the 'all' parameter will show you every permission path on the server that includes the 'domain\login'. 

    Using sp_helplogins will show you the database membership that the login has.

    Here is another way to determine how the login is making it into the Server and the database. 

    EXECUTE AS LOGIN = 'domain\login'
    GO
    SELECT name AS UserName, type_desc FROM sys.database_principals
    WHERE is_member(name) = 1 
    ORDER BY type_desc, UserName
    SELECT name AS LoginName, type_desc FROM sys.server_principals
    WHERE is_member(name) = 1 
    ORDER BY type_desc, LoginName
    GO
    REVERT
    Go

    I sometimes use this approach when I want to build up a result set in a table for further processing.

    RLF

    Tuesday, June 18, 2013 2:17 PM
  • Bumping.

    Suggestions on this will be appreciated.


    Tuesday, June 18, 2013 2:26 PM
  • Did you miss my post from about 9 minutes before your 'bump'?  Or do I still not understand the problem?

    RLF

    • Edited by SQLWork Tuesday, June 18, 2013 6:07 PM clarify question
    Tuesday, June 18, 2013 4:41 PM
  • Hey Russel, I appreciate your time on digging futhur.

    Actully, I'm talking about the database user, NOT login. Types of 'S' and 'U' stand for 'SQL_USER' and 'WINDOWS_USER' in sys.database_principals (This is a database level DMV). 

    The thing here is to identify orphaned database user and database user without login. I want to drop orphaned database user, and keep database user without login on each database.

    I'm good on picking up orphaned database user of 'SQL_USER' (type='S'). As you mentioned, as a SQL_USER user created without login, the lenght of its SID is 28 on sys.database_principals, but for a orphaned SQL_USER user, the SID length is 16. According to this, I can easyly find out orphaned SQL_USER user.

    However, for WINDOWS_USER user, the lenght of SID on sys.database_principals is 28 for both orpahned and without login user. To be worse, the orphaned user like [DOMAIN1\user1], it's also able to pass the query - 'EXECUTE AS USER = [DOMAIN1\user1]', because it's a memebr of Windows Group login - [DOMAIN1\group1]. I was expecting an error if the orphaned user  [DOMAIN1\user1] is not as a member of any Windows Group login, so I can definitely say it's a orphaned user. 

    The good news is, it seems able to identify the Windows orphaned user by xp_logininfo under a user database - db_user.

    1. For orphaned Windows user with Windows login - [DOMAIN1\user1] dropped, and [DOMAIN1\user1] is a member of [DOMAIN1\group1] :

    exec xp_logininfo '[DOMAIN1\user1]', 'all'

    account name type privilege mapped login name permission path
    DOMAIN1\user1 user admin DOMAIN1\user1 DOMAIN1\group1

    2. For a normal Windows user with Windows login - [DOMAIN1\user2], and [DOMAIN1\user2] is a member of [DOMAIN1\group1]:

    exec xp_logininfo '[DOMAIN1\user2]', 'all'

    account name type privilege mapped login name permission path
    DOMAIN1\user2 user user DOMAIN1\user2 NULL
    DOMAIN1\user2 user user DOMAIN1\user2 DOMAIN1\group1

    3. For a Windows user created without Windows login - [DOMAIN1\jack], and [DOMAIN1\jack] is not a member of any Windows group login:

    exec xp_logininfo '[DOMAIN1\jack]', 'all'

    account name type privilege mapped login name permission path

    Based on the results above, we can tell: no record returned is the Windows user created without login; having no record with NULL 'permission path' is orphaned Windows user; having record with NULL 'permission path' is normal Windows user.

    Hope this thread is clear enough to you. 

    Does this make sense, or you have better idea?

    Thanks again!





    • Edited by Stephanie Lv Wednesday, June 19, 2013 5:30 AM alter
    Wednesday, June 19, 2013 2:50 AM
  • Damn! Another problem here.

    4. For orphaned Windows user with Windows login - [DOMAIN1\tom] dropped, and [DOMAIN1\tom] is not a member of any Windows group login :

    exec xp_logininfo '[DOMAIN1\tom]', 'all'

    account name type privilege mapped login name permission path

    It's also not able to distinguish 3/4.


    • Edited by Stephanie Lv Wednesday, June 19, 2013 5:34 AM typo
    Wednesday, June 19, 2013 5:33 AM
  • Stephanie - Coming back around on the orphaned users question.  I do not believe that you can answer all of your questions just by using the state of information in the SQL Server. 

    Every Windows Login in a database was once a valid login.

    If a Windows Login was a member of a Windows Group that was granted access to a database, there may never be a user for that specific Windows Login. 

    Note, however, if a User chooses a different default schema or becomes the owner of a schema, a user (automatically created with the Windows Login name) will be needed to hold that information in sys.database_principals.

    Another factor in examining sys.server_principals and sys.database_principals is that the SQL Server is not aware of changes on the domain.  If a Group or Login is renamed, the server does not know.  If it is deleted from the domain, the SQL Server does not know. 

    Actually the SQL Server does not care if the Group or Login was renamed, since it actually uses the SID.  As long as the SID exists, no matter the name, everything will still connect.  (If a Group or Login is moved to a different domain, the GUID is supposed to be stable, but the SID will change.)

    So, xp_lgoininfo and sp_helplogins are often helpful.  But these commands are SQL Server centric and do not reveal everything happening on the domain.

    In your case 4, did you try running my EXECUTE AS LOGIN script against [DOMAIN1\Tom]?  I am curious if it returned anything useful.  

    If you really want to know the state of the potentially orphaned user on the domain, you probably will need to also examine the Active Directory, looking for the name and/or the SID.

    Then you have to make your best decision and move ahead. 

    RLF

    • Proposed as answer by Fanny Liu Thursday, June 27, 2013 7:25 AM
    Wednesday, June 26, 2013 2:02 PM
  • 'EXECUTE AS LOGIN' will not help on case 3/4 because both of them have no login. And both of them have the right SID on sys.database_principals.

    I think the problem here is nothing to do with SID.


    MCSE:Data Platform


    Friday, June 28, 2013 2:46 AM
  • Right.  As I mentioned, there is not necessarily enough information for you to know what happened.

    If a user has an active SID, but that user no longer has a Login (Windows Login or Windows Group) that gives it access to the server, then your knowledge is restricted.  If you know that the SID exists on the domain, then you know the origin of the user, but without a login (i.e. server access) the user is useless.

    FWIW,

    RLF

    Friday, June 28, 2013 12:22 PM