User Name versus Login Name

• DB_Newbie2007

  SSCarpal Tunnel

  Points: 4438

  More actions

  October 20, 2009 at 1:55 pm

  #211312

  I have a User who recently was married and how wishes to change her SQL login name to the new married name.

  Changing the login name appears relatively straight-forward:

  ALTER LOGIN Smith WITH NAME = Jones;

  However, I noticed that all of the databases she is a member of still show her as Smith (The login name changed in the properties to "Jones" but the User Name did not change).

  Is there a built in routine or function to also change the User name?

  I would have hoped that an alter of the Server-based Login name would also have modified all of the Database-level User Names as well...

  Thanks!

  Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  October 21, 2009 at 6:44 am

  #1068817

  ALTER USER Smith WITH NAME = Jones

  ---------------------------------------------------------------------

• DB_Newbie2007

  SSCarpal Tunnel

  Points: 4438

  More actions

  October 21, 2009 at 7:43 am

  #1068864

  Thanks! 🙂

  I was hoping the solution might be something simple. Here we go:

  EXEC sp_msforeachDB 'USE ?; DECLARE @Name VARCHAR(30), @NewName VARCHAR(30), @Statement varchar(1000);

  SET @Name = ''Smith''; SET @NewName = ''Jones'';

  IF EXISTS (SELECT * FROM sys.sysusers WHERE NAME = @Name)

  BEGIN

  PRINT ''The Name ''+@Name+'' Exists in ''+db_name();

  SET @Statement = N''ALTER USER ''+@Name+'' WITH NAME = ''+@NewName+''''

  EXEC (@Statement);

  --PRINT @Statement;

  END;'

  Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)