locked
Databaselogin after SAMAccountname renaming still works RRS feed

  • Question

  • Hi there

    Situation:
    Got a sql 2005 server with users who are connecting with windows integrated authentication.
    we renamed the user contosouser1 to contoso20128.

    Problem:
    When the user starts the application, it still connects with the old username to the databse successfully.

    How do i know? I checked the Profiler and i found the following:
    EventClass: RPC:Completed
    TextData: exec Reporting_GetRequestsViewForClient 0,N'contoso20128'
    ApplicationName: Contoso Cristal...
    NTUserName: ContosoUser1

    Question:
    How could that happends, cause the user is already renamed?
    So, it should not be possible to log in to the database with old account information.

    Any Ideas?


    Add:
    The longer I wait, the more users will be authenticated with the new SAMAccount-Names
    Monday, June 8, 2009 11:53 AM

Answers

  •   As TiborK mentioned, the SID is still the same. The most likely reason SQL Server information is still showing the previous name for the account is because the login MD stored in SQL Server includes the “friendly name” for the account and it is using this copy of the data.

       You can use ALTER LOGIN …. WITH NAME DDL syntax to change the login name to match the new (i.e. real) Windows name. For details please refer to BOL: http://msdn.microsoft.com/en-us/library/ms189828.aspx

      I hope this information is useful,

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 9, 2009 1:16 AM

All replies

  • Renaming the Windows account doesn't change the SID number, and the connection is done using the SID number. I.e., this is expected behavior.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, June 8, 2009 7:04 PM
  •   As TiborK mentioned, the SID is still the same. The most likely reason SQL Server information is still showing the previous name for the account is because the login MD stored in SQL Server includes the “friendly name” for the account and it is using this copy of the data.

       You can use ALTER LOGIN …. WITH NAME DDL syntax to change the login name to match the new (i.e. real) Windows name. For details please refer to BOL: http://msdn.microsoft.com/en-us/library/ms189828.aspx

      I hope this information is useful,

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 9, 2009 1:16 AM
  • Thanks for the answer. Yes, I knew that the SID is still the same.

    So if that's the cause, why the samaccontname is updating time after time, am I able to force the refresh of the old usernames to the new usernames (SAMAccountName)?

    Thanks for any input.

    Tuesday, June 9, 2009 11:34 AM
  • <<So if that's the cause, why the samaccontname is updating time after time, am I able to force the refresh of the old usernames to the new usernames (SAMAccountName)?>>

    No sure what you are asking, but here's spething that might or might not be useful:

    Check out sp_validatelogins. It will report logins whos SID no longer exist in Windows, and also report login whos name no longer match (renamed).
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, June 9, 2009 2:34 PM