代码
--
在目标库上执行以下语句,需要改 库地址以及连接用的, 用户名 、密码
exec sp_addlinkedserver @server = ' 192.168.0.135,1027 ' -- 源数据库
go
exec sp_addlinkedsrvlogin @rmtsrvname = ' 192.168.0.135,1027 ' ,
@useself = false,
@locallogin = ' sa ' ,
@rmtuser = ' sa ' , -- 用户名
@rmtpassword = ' ' -- 密码
go
insert into U_ADMIN.T_PD_USER_MASTER(UM_LOGIN_ID, UM_NAME, UM_PASSWORD, UM_GROUP_OR_USER, UM_JOB, UM_TEL, UM_ZJM_PY, UM_DEPT)
select NEWID (), um_name, UM_PASSWORD, UM_GROUP_OR_USER, UM_JOB, UM_TEL, UM_ZJM_PY, UM_DEPT from [ 192.168.0.135,1027 ] .dei_net_szxq.dbo.user_master
go
select BMMC from U_ADMIN.T_PD_DEPARTMENT where BMMC in (
SELECT code_tbl.display FROM [ 192.168.0.135,1027 ] .dei_net_szxq.dbo.code_tbl WHERE code_tbl.ename = ' department '
)
go
-- 更新部门
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' 0f884a6e-694a-4aa1-84db-494843ded9b4 ' where UM_DEPT = ' 0 ' -- 局长
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' 9ea19025-a228-4f43-9627-4baa19d86f61 ' where UM_DEPT = ' 1 ' -- 办公室
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' a4f68bbb-be74-4ed8-8dc2-867d88cda49f ' where UM_DEPT = ' 2 ' -- 开发处
-- 新系统中部门叫开发管理处,而老系统分为 开发处,和管理处
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' a4f68bbb-be74-4ed8-8dc2-867d88cda49f ' where UM_DEPT = ' 3 ' -- 管理处,
-- 监察大队未找到对应关系
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = null where UM_DEPT = ' 4 '
-- 原系统有两个名为监测站的部门
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' eaa0a9fe-c4a1-49b9-9f3f-e36efaae37ff ' where UM_DEPT = ' 5 '
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' eaa0a9fe-c4a1-49b9-9f3f-e36efaae37ff ' where UM_DEPT = ' 6 '
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' 0c7d83df-bd15-4f71-b090-e87edb5e8a01 ' where UM_DEPT = ' 7 ' -- 信访科
go
update U_ADMIN.T_PD_USER_MASTER set UM_ROLE = ' 69eaef6c-59bc-4264-ab86-265ab34c2e4d ' where UM_DEPT = ' 0f884a6e-694a-4aa1-84db-494843ded9b4 '
-- 其他用户没有找到权限的对应关系 ,统一设置成中心收文员, 原表和新表对应关系需要对应
update U_ADMIN.T_PD_USER_MASTER set UM_ROLE = ' 41d9cff3-0d2c-4447-a78e-af22c341e8d7 ' where
UM_DEPT in ( ' 9ea19025-a228-4f43-9627-4baa19d86f61 ' , ' a4f68bbb-be74-4ed8-8dc2-867d88cda49f ' , ' a4f68bbb-be74-4ed8-8dc2-867d88cda49f '
, ' eaa0a9fe-c4a1-49b9-9f3f-e36efaae37ff ' , ' 0c7d83df-bd15-4f71-b090-e87edb5e8a01 ' ) and UM_ROLE is null
go
-- 关闭连接
sp_droplinkedsrvlogin @rmtsrvname = ' 192.168.0.135,1027 ' , @locallogin = ' sa '
go
sp_dropserver @server = ' 192.168.0.135,1027 '
go
exec sp_addlinkedserver @server = ' 192.168.0.135,1027 ' -- 源数据库
go
exec sp_addlinkedsrvlogin @rmtsrvname = ' 192.168.0.135,1027 ' ,
@useself = false,
@locallogin = ' sa ' ,
@rmtuser = ' sa ' , -- 用户名
@rmtpassword = ' ' -- 密码
go
insert into U_ADMIN.T_PD_USER_MASTER(UM_LOGIN_ID, UM_NAME, UM_PASSWORD, UM_GROUP_OR_USER, UM_JOB, UM_TEL, UM_ZJM_PY, UM_DEPT)
select NEWID (), um_name, UM_PASSWORD, UM_GROUP_OR_USER, UM_JOB, UM_TEL, UM_ZJM_PY, UM_DEPT from [ 192.168.0.135,1027 ] .dei_net_szxq.dbo.user_master
go
select BMMC from U_ADMIN.T_PD_DEPARTMENT where BMMC in (
SELECT code_tbl.display FROM [ 192.168.0.135,1027 ] .dei_net_szxq.dbo.code_tbl WHERE code_tbl.ename = ' department '
)
go
-- 更新部门
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' 0f884a6e-694a-4aa1-84db-494843ded9b4 ' where UM_DEPT = ' 0 ' -- 局长
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' 9ea19025-a228-4f43-9627-4baa19d86f61 ' where UM_DEPT = ' 1 ' -- 办公室
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' a4f68bbb-be74-4ed8-8dc2-867d88cda49f ' where UM_DEPT = ' 2 ' -- 开发处
-- 新系统中部门叫开发管理处,而老系统分为 开发处,和管理处
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' a4f68bbb-be74-4ed8-8dc2-867d88cda49f ' where UM_DEPT = ' 3 ' -- 管理处,
-- 监察大队未找到对应关系
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = null where UM_DEPT = ' 4 '
-- 原系统有两个名为监测站的部门
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' eaa0a9fe-c4a1-49b9-9f3f-e36efaae37ff ' where UM_DEPT = ' 5 '
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' eaa0a9fe-c4a1-49b9-9f3f-e36efaae37ff ' where UM_DEPT = ' 6 '
update U_ADMIN.T_PD_USER_MASTER set UM_DEPT = ' 0c7d83df-bd15-4f71-b090-e87edb5e8a01 ' where UM_DEPT = ' 7 ' -- 信访科
go
update U_ADMIN.T_PD_USER_MASTER set UM_ROLE = ' 69eaef6c-59bc-4264-ab86-265ab34c2e4d ' where UM_DEPT = ' 0f884a6e-694a-4aa1-84db-494843ded9b4 '
-- 其他用户没有找到权限的对应关系 ,统一设置成中心收文员, 原表和新表对应关系需要对应
update U_ADMIN.T_PD_USER_MASTER set UM_ROLE = ' 41d9cff3-0d2c-4447-a78e-af22c341e8d7 ' where
UM_DEPT in ( ' 9ea19025-a228-4f43-9627-4baa19d86f61 ' , ' a4f68bbb-be74-4ed8-8dc2-867d88cda49f ' , ' a4f68bbb-be74-4ed8-8dc2-867d88cda49f '
, ' eaa0a9fe-c4a1-49b9-9f3f-e36efaae37ff ' , ' 0c7d83df-bd15-4f71-b090-e87edb5e8a01 ' ) and UM_ROLE is null
go
-- 关闭连接
sp_droplinkedsrvlogin @rmtsrvname = ' 192.168.0.135,1027 ' , @locallogin = ' sa '
go
sp_dropserver @server = ' 192.168.0.135,1027 '
go