ALTER USER WITH LOGIN renames the user

• Dave Dustin

  Say Hey Kid

  Points: 688

  More actions

  July 3, 2011 at 5:48 pm

  #243753

  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

• Dave Dustin

  Say Hey Kid

  Points: 688

  More actions

  July 3, 2011 at 6:33 pm

  #1347601

  And it turns out this behaviour is "documented" in BOL

  http://msdn.microsoft.com/en-us/library/ms176060.aspx

  Looks like my offline copy hasn't had the change applied to it.

  AucklandSQL User Group
  Independent SQL Server Consultant
  SQL Server MVP