Hi All,
Working with SQL2005-SP2, I’m running into a situation where doing an ALTER USER <user> WITH LOGIN=[domain\user] is remapping the <user> to be the same as the LOGIN name after restoring the database from one server to another.
Server 1:
CREATE LOGIN [server1\groupname] FROM WINDOWS ;
use [database] ;
CREATE USER [GroupName] FOR LOGIN [server1\groupname] ;
GO
So we have a login called [server1\groupname] and a database user called [GroupName]
Backup database
Server 2:
CREATE LOGIN [server2\groupname] FROM WINDOWS ;
Restore database
use [database] ;
ALTER USER [GroupName] WITH LOGIN [server2\groupname] ;
Doing that, the database user is now [server2\groupname].
If I use
ALTER USER [GroupName] WITH LOGIN [server2\groupname], NAME = [GroupName] ;
it works fine, keeping the name as previously.
Any ideas?
Cheers
AucklandSQL User Group
Independent SQL Server Consultant
SQL Server MVP