Migration Login and User steps

To copy over the Password for a login do the folling:

  • On the source instance run:
    SELECT convert(varbinary(256),password) as password FROM master..syslogins
    WHERE name = ;
  • On the destination instance run:

To remove users you may need to remove old schemas first and you then need to first check that no objects belong to them:

  • First just try to drop the user normally
  • You may well get an error saying the user owns objects or schemas in the database
  • To check what schema the user owns run:
    SELECT name FROM sys.schemas WHERE principal_id = USER_ID(”);
  • Next check which objects are attached to this schema with:
    SELECT *
    FROM sys.objects
    WHERE schema_id = SCHEMA_ID(”);
  • You will now be able to drop the schema and then th euser form the database

To realign sysusers with their logins run:

 EXEC sp_change_users_login 'update_one', 'WFMUser', 'WFMUser';