【BOS基础系统_权限】sql脚本给用户批量分配默认角色

---批量给用户在默认组织下分配默认角色，在查询分析器按照以下步骤依次执行sql，注意步骤3需要替换两处默认角色fid---

--1、备份表

SELECT * into T_pm_userroleorg_bk220528 from T_pm_userroleorg;

--2、查询默认角色fid

SELECT FID  FROM T_pm_role where FISDEFROLE =1;

--3、将上一步查到的默认角色fid替换到下面两处

SELECT distinct org.fuserid,'此处替换默认角色fid' as froleid ,org.forgid  into t_pm_userroleorg_bk6 FROM T_pm_orgrange org

inner join t_pm_user u on u.FID  = org.FUSERID and  u.FDEFORGUNITID = org.FORGID

left join t_pm_userroleorg uro on uro.FUSERID = u.FID 

 where u.FTYPE = 20 and org.FTYPE = 10 and not exists(SELECT 1 FROM T_pm_userroleorg uro2 where uro2.FUSERID = uro.fuserid and uro2.FROLEID = '此处替换默认角色fid' and uro2.FORGID = uro.forgid);

--4、上面可能存在已经分配过的数据，提取出为分配的数据

SELECT * into t_pm_userroleorg_bk7 FROM t_pm_userroleorg_bk6 urobk6 where not exists (SELECT 1 FROM T_pm_userroleorg where FUSERID = urobk6.fuserid and FROLEID = urobk6.FROLEID and FORGID = urobk6.forgid )

--5、给所有职员类型用户的默认组织下分配默认角色

INSERT INTO T_pm_userroleorg (FUSERID ,FROLEID ,FORGID ) SELECT FUSERID ,FROLEID ,FORGID FROM t_pm_userroleorg_bk7;