DECLARE @STATE AS  VARCHAR(4000),  @SJ_USERID INT

SELECT @SJ_USERID=1

 

/*

  Security User by groups  

*/

-- delete not existed groups

DELETE FROM [DBO].[SECURITY_JOINUSERSGROUPS] WHERE

[SJ_USERID]=@SJ_USERID AND [SJ_GROUPID] NOT IN (1,2)

 

-- add new groups

INSERT INTO [DBO].[SECURITY_JOINUSERSGROUPS] ([SJ_USERID], [SJ_GROUPID])

  SELECT @SJ_USERID, GP.SG_GROUPID FROM [DBO].[SECURITY_GROUPS] AS GP WHERE GP.SG_GROUPID IN (1,2)

    AND GP.SG_GROUPID NOT IN (SELECT DISTINCT [SJ_GROUPID] FROM [DBO].[SECURITY_JOINUSERSGROUPS] WHERE [SJ_USERID]=@SJ_USERID)

 

/*

  Security Processes by groups

*/

DECLARE @SR_GROUPID INT

SELECT @SR_GROUPID=1

-- delete not existed process for the specific group

DELETE FROM [dbo].[SECURITY_RIGHTS] WHERE

[SR_GROUPID]=@SR_GROUPID AND [SR_PROCESS] NOT IN (1,2,3,4)

 

-- add new processes for the specific group

INSERT INTO [DBO].[SECURITY_RIGHTS] ([SR_GROUPID],[SR_PROCESS])

  SELECT @SR_GROUPID, PR.[SP_SPID] FROM [dbo].[SECURITY_PROCESSES] AS PR WHERE PR.SP_SPID IN (1,2,3,4)

    AND PR.SP_SPID NOT IN (SELECT DISTINCT [SR_PROCESS] FROM [DBO].[SECURITY_RIGHTS] WHERE [SR_GROUPID]=@SR_GROUPID)

 

/*

  Security Processes by user

*/

DECLARE @SR_USERID INT

SELECT @SR_USERID=1

-- delete not existed process for the specific user

DELETE FROM [dbo].[SECURITY_RIGHTS] WHERE

[SR_USERID]=@SR_USERID AND [SR_PROCESS] NOT IN (3,4)

 

-- add new processes for the specific user

INSERT INTO [DBO].[SECURITY_RIGHTS] ([SR_USERID],[SR_PROCESS])

  SELECT @SR_USERID, PR.[SP_SPID] FROM [dbo].[SECURITY_PROCESSES] AS PR WHERE PR.SP_SPID IN (3,4)

  AND PR.SP_SPID NOT IN (SELECT DISTINCT [SR_PROCESS] FROM [DBO].[SECURITY_RIGHTS] WHERE [SR_USERID]=@SR_USERID)

 

-- 19/02/2009 15:19:05: post edited by sergey.