Recently, a client asked that I grant database access for several of their new employees. The client uses SQL Server 2000 and has an Active Directory in place to manage domain users and resources. Windows Authentication is used almost exclusively in this environment.
As I dutifully created the new Logins, granted access to the required databases, and assigned the users to the appropriate database roles, everything went as expected - that is, until I got to the last two employees on the list. As I attempted to create a new Login for those two employees, I received the following error:
Error 15401: Windows NT user or group MYCLIENT\Sally not found. Check the name again.
Fortunately, I had seen this error before and knew its likely cause.
Each user in Active Directory is given a unique security identifier, or SID. It's statistically impossible for any two users to receive the same SID. A SID is associated with a user account throughout its existence. SIDs are the reason why you cannot simply create a new user account of the same name to replace a user account that was inadvertently deleted and expect the permissions and group membership to transfer over to the new user account.
A SID also persists through name changes to the user account. If a domain user account is renamed to something completely different, its SID remains the same. If you rename a user account from John Brown to Sally Smith, the Active Directory considers this the same user account.
That's what happened in this case. Although the last two names on the list were new hires for the company, they were hired to replace former employees. Unbeknownst to me, the Domain Administrator did not create new user accounts for these individuals, rather he renamed the user accounts of the former employees that these new hires replaced. This left their SID intact.
The former employees had database Logins that used Windows Authentication. When I attempted to create a Login for the new employees, SQL Server realized that the SID already existed and it threw Error 15401.
To resolve this, I identified and deleted the former employee Logins that these two new hires were to replace. Then I could create a new Login for the new employees. Problem solved.
Obviously the best scenario would be to avoid this situation altogether. Proper change management procedures would have allowed me to delete the former Logins when the employees left the company, preventing this minor incident while making for a more secure database installation as well.
For more information, see Knowledge Base article 324321.
Cheers!
Joe
