【数据库】SQLServer数据库迁移后，同步登录名和密码

 

1. 查询用户名，密码，sid

select  convert(varbinary(255), isnull(password, '')) passwd,
        sid,name,loginname,password 
from syslogins 
WHERE password IS NOT NULL

2. 创建登录名与密码。PWD通过二进制获取

declare @pwd sysname
set @pwd=CONVERT(varbinary(256),
0x020052BFA665671089A8BCEED37AD15285A649E57B202DFC349A73A0BD32E2B8410F8E0271078D1083FD2802698D3D6098665F7BEB6AAF219D3F7CE79736A0SS31B6A617499O)
EXEC master..sp_addlogin 'username1', @pwd, @sid = 0x0BBB360D0A32604E8B62567HD13ABC58, @encryptopt = 'skip_encryption'

3. 自动匹配登录名与用户名
 

exec sp_change_users_login 'auto_fix','username1'