locked
Renamed AD user loses access to SQL RRS feed

  • Question

  • When we rename an AD user account, that user can no longer access SQL server (2000 and 2005).  When trying to add the user using DOMAIN\Username, SQL reports "Windows NT user or group 'DOMAIN\Username' not found.  Check the name again."

    What's the correct way to fix this?  We struggle with it everytime an employee gets married and usually end up deleting their account and recreating it.
    • Moved by Xinwei Hong - MSFT Tuesday, August 4, 2009 6:24 PM (From:SQL Server Data Access)
    Thursday, July 30, 2009 10:37 PM

Answers

  • Hi MadMikey

    More details would be helpful, though I'll make a few points:

    1. If the user already has access to the database, then renaming the AD Account will not (should not) make any difference. Their access is based on the SID, which doesn't change. Their login will still have the old name if you look in SSMS, but it links to the renamed AD account.

    2. Deleting and re-adding the new account will probably work, but you lose all of the database group memberships and permissions that the original user had applied, which may be a headache. I can't remember exactly, but this error may happen when the original user still exists in syslogins and you try to add the new user (since the two SIDs are the same). But refer to point 1.

    3. Best-practive for server access is to use Domain Groups, not individual users. So if a user gets renamed, (or replaced) then this issue doesn't arise.

    HTH

    Ewan







    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, August 5, 2009 11:32 AM

All replies

  • Is the user getting access through group membership?
    Start troubleshooting from the OS level - does windows 'see' the new name?

    Thanks,

    Varsham Papikian, New England SQL Server User Group Executive Board, USA: www.nesql.org; http://www.linkedin.com/in/varshampapikian
    Sunday, August 2, 2009 10:09 PM
  • Hi,

    I'm not sure if I've misunderstood your problem. From your description, it seems that one of your domain users can't access the database after they had been renamed, right?

    I suggest that you can use administrator login to access your database, and in the login node of database security, you can delete the orignal domain user, if they are in some gourps, you can go to computer management console panel to remove them. And then, add the new domain users into the SQL logins or the group which is able to access the database.

    Thanks.
    Microsoft Online Community Support Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, August 5, 2009 8:05 AM
  • Hi MadMikey

    More details would be helpful, though I'll make a few points:

    1. If the user already has access to the database, then renaming the AD Account will not (should not) make any difference. Their access is based on the SID, which doesn't change. Their login will still have the old name if you look in SSMS, but it links to the renamed AD account.

    2. Deleting and re-adding the new account will probably work, but you lose all of the database group memberships and permissions that the original user had applied, which may be a headache. I can't remember exactly, but this error may happen when the original user still exists in syslogins and you try to add the new user (since the two SIDs are the same). But refer to point 1.

    3. Best-practive for server access is to use Domain Groups, not individual users. So if a user gets renamed, (or replaced) then this issue doesn't arise.

    HTH

    Ewan







    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, August 5, 2009 11:32 AM
  •   As Ewan mentioned, the authentication should be based exclusively on SIDs (not the NetBIOS names), and the user should still be able to connect.

      The problem will be when trying to access/manipulate metadata since SQL Server will use the metadata (MD) copy of the NetBIOS name (i.e. the old name) and we won’t be able to identify the new name. The root cause for this problem is the fact that SQL Server keeps a copy of the Windows NetBIOS user name in MD and we use this MD copy whenever a name-based operation on the user is made instead of relying exclusively on the SID. This is a well known limitation of SQL Server and it has been there since Windows authentication was introduced (SQL Server 7 if I do remember).

      In SQL Server 2005 SP2 we introduced a mechanism that allows you to rename the login and user to match the updated Windows name:

    ALTER LOGIN [Old_Windows_name] WITH NAME = [New_Windows_name]
    ALTER USER [Old_Windows_name] WITH NAME = [New_Windows_name]

      As long as the SID in MD for [Old_Windows_name] matches the SID for [New_Windows_name], this operation should succeed and properly rename the principal login/user name in MD. For more information please refer to BOL:
    http://msdn.microsoft.com/en-us/library/ms189828.aspx
    http://msdn.microsoft.com/en-us/library/ms176060.aspx

      I hope this information helps,
     -Raul Garcia
      SDE/T
      SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, August 5, 2009 9:42 PM
  • Thanks guys!  Agreed.  We should be using domain groups rather than individual users, and we'll make it a point to change that.  In short, we deleted and re-created the account, but this will help us in the future.

    Thanks again for the info.
    Thursday, August 6, 2009 3:12 PM