/* map a login to a new SID after the login is changed, use sp_change_users_login to correct the mapping between the login and it's database users */ declare @newsid uniqueidentifier, @user varchar(30), @login varchar(30), @sql$ varchar(200), @Update$ varchar(200) set @newsid = 0x4FEF7B3DA31E564A85C8F20CA71BD1E4 set @user = 'test' set @login = @user set @sql$ = 'use ? if exists (select 1 from sysusers where name = ''' + @user + ''') exec sp_change_users_login ''Update_One'',''' + @user + ''',''' + @login + '''' if not exists (select 1 from master.dbo.sysxlogins where sid = @newsid) begin exec master.dbo.sp_configure 'allow updates', '1' RECONFIGURE WITH OVERRIDE exec master.dbo.sp_executesql N'update master.dbo.sysxlogins set sid = @newsid where name = @user', N'@newsid uniqueidentifier, @user varchar(30)', @newsid, @user exec master.dbo.sp_configure 'allow updates', '0' RECONFIGURE WITH OVERRIDE exec master.dbo.sp_msforeachdb @sql$ end else print 'SID is already in use on this server' use admin exec sp_change_users_login 'test','AUTO_FIX'