We have a SQL 2012 database and our users are set up via Windows Authentication to connect to a specific database - say DatabaseA.
It looks like this: Windows user "DomainA/abcd", is mapped to a user "abcd" in DatabaseA, which has a default schema "abcd" which in turn owns a bunch of tables. Remote users log into their laptops (as DomainA/abcd), connect via VPN and are able to query data from the SQL server (they pull data from their default schema, based on their Windows login).
It all works fine, however, now we are moving to a new domain.
Our users will be logging in as "DomainB/abcd" and when they try to connect to their tables on the server, no data is being returned since "DomainA/abcd" and "DomainB/abcd" are different users.
What am I trying to achieve: Can I get the DB user "abcd" which is currently mapped to Windows login "DomainA/abcd", to point to Windows login "DomainB/abcd". Is that even possible?
If not, what are the alternatives. We want to avoid dropping the schema/tables and the user and then recreate everything again.
What have I tried: I tried ALTER USER and sp_change_users_login to no avail.
ALTER USER [DomainB\abcd] WITH LOGIN = [abcd] returns Cannot remap user to login 'abcd', because the login is already mapped to a user in the database. - Makes perfect sense.
EXEC sp_change_users_login 'Update_One', 'abcd', 'DomainB\abcd'; GO returns Terminating this procedure. The User name 'abcd' is absent or invalid. - MSDN says this could be used to map a db user to a new SQL login, no mention of a Windows login.
I'm a member of the sysadmin role on the DB server, so I don't think that it is a permission issue.
When SQL Server user domain name changes, at this time, we still want to keep the current database user setting and don't want to recreate those users, we can use SQL script to update the user domain as follow:
ALTER USER [abcd] WITH LOGIN = [DomainB\abcd], NAME = [abcd]
I am glad that works for you.