I am trying to change a SQL 2000 Server window authenticated login linked to a database user. I know there is a method call sp_change_user_login but this only work with SQL authenticated login. I can't drop and recreate the database user with the new window authenticated login cause I need to retain its security properties.
From a text book I read, it says that database user are linked not to the name of the login but the SID of the login. So I was thinking of trying to change the database user SID with the new window login SID to link the database user to this new window authenticated login, is this what I should be doing, what did you guys do to remap a database user with a new window authenticated login?
Personally, I don't. I grant permissions to a role, and add or remove members as necessary. You may want to use Enterprise Manager to script out all the permissions in the database, and use that to create an appropriate role.