locked
When a Domain user reuse the same User login in SQL Server 2000 RRS feed

  • Question

  • Hello to all,

     

    I have some probles with user domains.

     

    I have a domain user like ROYAL\dcoria (Daniel Coria) and I set up this user in my SQL Server 2000 SP using windows authentication one year ago, all my tables has a column named CrBy Int and this columns has a default (user_id()) with this every record inserted in my table has de ID for ROYAL\dcoria.

     

    Now 1 month ago this user was fired from the company, now the new employee hired his name is David Coria and our Network department only have renamed the domain information for user ROYAL\dcoria with the new information from David Coria, and I need to set up again in my SQL Server 2000 the new hired employee.

     

    How can I made the diference between ROYAL\dcoria (Daniel Coria)  and ROYAL\dcoria(David Coria) in my sql server 2000, due our network department reuse the user logins in the active directory

     

    Regards.

    Friday, August 15, 2008 5:55 PM

Answers

  •  The root cause of this problem is that even though the SIDs for both Windows users are different (effectively making them two completely different security principals in Windows), SQL Server will also perform some metadata constraints based on the name.

     

       I am assuming that the old SID for dcoria (Daniel) was completely deleted from the AD, and given that you are using SQL Server 2000, I would recommend trying to delete the old account for dcoria before creating the new one. You may need to change ownership of any objects owned by this principal using sp_changeobjectowner before dropping the user and login.

     

      I would strongly suggest switching (if possible) to use the SID (suser_sid()) instead of the user ID. Users IDs are not uniquely identifiable as user IDs are recycled (i.e. if a user is deleted, the next user being created will reuse the user ID to fill the gap).

     

      Unfortunately for any data that you may be storing using the user ID, it would be a more difficult to find a solution as it would really depend on how the data is used and if there is a timestamp associated with it (i.e. if there is a timestamp associated with any entry pointing to dcoria, it may be possible to distinguish between the two users).

     

      Another alternative (not a pretty one though) is to create a dummy user to fill the user ID gap, just make sure that this dummy user has no privileges on the database, and if possible drop the login (unfortunately I don't have a SQL Server 2000 server at hand right now to test this one myself).

     

      I hope this helps,

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Friday, August 15, 2008 7:12 PM

All replies

  •  The root cause of this problem is that even though the SIDs for both Windows users are different (effectively making them two completely different security principals in Windows), SQL Server will also perform some metadata constraints based on the name.

     

       I am assuming that the old SID for dcoria (Daniel) was completely deleted from the AD, and given that you are using SQL Server 2000, I would recommend trying to delete the old account for dcoria before creating the new one. You may need to change ownership of any objects owned by this principal using sp_changeobjectowner before dropping the user and login.

     

      I would strongly suggest switching (if possible) to use the SID (suser_sid()) instead of the user ID. Users IDs are not uniquely identifiable as user IDs are recycled (i.e. if a user is deleted, the next user being created will reuse the user ID to fill the gap).

     

      Unfortunately for any data that you may be storing using the user ID, it would be a more difficult to find a solution as it would really depend on how the data is used and if there is a timestamp associated with it (i.e. if there is a timestamp associated with any entry pointing to dcoria, it may be possible to distinguish between the two users).

     

      Another alternative (not a pretty one though) is to create a dummy user to fill the user ID gap, just make sure that this dummy user has no privileges on the database, and if possible drop the login (unfortunately I don't have a SQL Server 2000 server at hand right now to test this one myself).

     

      I hope this helps,

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Friday, August 15, 2008 7:12 PM
  • Thank you very much Raul.

     

    Now I will try to test your recomendations.

     

    So, what about your blog at blosg.microsoft.com/raulga, please, share with us as soon as possible more articles your blog is a very usefull information for me due I am a novice DBA.

     

    Best regards.

    Friday, August 15, 2008 7:38 PM
  •    I am glad to be able to help. I decided to post all my SQL related articles and comments to the SQL Server Security blog instead of my personal blog, that way it should be easier to find all SQL Server security team posts.

     

      I apologize for the lack of activity in the last few months, but we were focused on shipping SQL Server 2008; but now that SQL Server 2008 was released to manufacturing, I think we will be able to be active in the blog once again.

     

      Thanks a lot for your support and your feedback, and please let us know if the recommendation helped and/or if you have further questions.

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Friday, August 15, 2008 8:52 PM