Deleted sql server windows authentication user

Question

I had deleted a system user "sysuser" from the server. I had previously used this user to login to SQL Server through "Windows Authentication" mode.

Now, I tried to create a new user with the same username and password but am unable to login to the SQL Server again.

I deleted the user in the server from control panel. Then I log in to SQL Server using windows authentication but cannot log in. I tried to add back the user with same password using control panel. I tried to log in SQL Server using windows authentication and the re-created user but still cannot log in.

Answer 1

When you add a Windows Authentication login to SQL Server, the login gets created using the Windows Security ID, or SID, of the Windows user to create the login.

The name of the login is irrelevant, since authentication occurs via the SID.

If you delete and re-create a Windows user that has a login in SQL Server, the newly created Windows user will have a different SID than the old user, and hence will not be linked to the SQL Server login. In order to have the new Windows user login to SQL Server, you need to create a new SQL Server login for the user via the CREATE LOGIN T-SQL statement, as in:

CREATE LOGIN <login_name>
FROM WINDOWS;