dbo.QBM_PDialogUserInGroupCorrect

1   create   procedure QBM_PDialogUserInGroupCorrect (@UID_DialogUserPattern varchar(38) = '%' ) as begin declare @XUser nvarchar(64) = object_name
2(@@procid) declare @XDate datetime = getutcdate() declare @defaultGroup varchar(38) = 'QBM-1CA5B3F72AD35643BEDB25511BB6DBF5' declare @Debugswitch int =
3 0 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @cmd nvarchar
4(max) SET XACT_ABORT OFF BEGIN TRY if @Debugswitch > 0 begin print 'entry : ' + @XUser end exec QBM_PSessionContextSet 'XUSER', @XUser  insert into DialogUserInGroup
5 ( UID_DialogGroup, UID_DialogUser, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, XObjectKey) select distinct G.UID_DialogGroup, U.UID_DialogUser
6, @XDate, @XDate, @XUser, @XUser , dbo.QBM_FCVElementToObjectKey2('DialogUserInGroup', 'UID_DialogGroup', G.UID_DialogGroup, 'UID_DialogUser', U.UID_DialogUser
7)  from DialogUser U join DialogGroup G on G.UID_DialogGroup = @defaultGroup where not exists (select top 1 1 from DialogUserInGroup UiG where UiG.UID_DialogUser
8 = U.UID_DialogUser and UiG.UID_DialogGroup = G.UID_DialogGroup ) if exists (select top 1 1 from QBM_VSchemaColumns c where c.TABLE_NAME = 'DialogGroup'
9 and c.COLUMN_NAME = 'IsRoleBasedOnly' ) begin if @Debugswitch > 0 begin print 'rolebased only exists : ' + @XUser end select @cmd = '
10	declare @XDate datetime = getutcdate()
11
12  insert into DialogUserInGroup ( UID_DialogGroup, UID_DialogUser, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, XObjectKey)
13      select distinct G.UID_DialogGroup, U.UID_DialogUser, @XDate, @XDate, '''
14 + @XUser + ''', ''' + @XUser + '''
15				  , dbo.QBM_FCVElementToObjectKey2(''DialogUserInGroup'', ''UID_DialogGroup'', G.UID_DialogGroup, ''UID_DialogUser'', U.UID_DialogUser)
16	  -- select g.GroupName, u.UserName, u.IsAdmin, u.IsDynamicUser 
17  from DialogUser U join DialogGroup G on 1=1
18   where u.IsAdmin = 1
19	      and u.IsDynamicUser = 0
20		  and u.IsServiceAccount = 0
21	      and u.UID_DialogUser like '''
22 + @UID_DialogUserPattern + '''
23	      -- diese Zeile ist der einzige Unterschied
24	      and g.IsRoleBasedOnly = 0
25	      and not exists (select top 1 1
26						      from DialogUserInGroup UiG
27						      where UiG.UID_DialogUser = U.UID_DialogUser
28							      and UiG.UID_DialogGroup = G.UID_DialogGroup
29					      )
30	'
31 exec sp_executesql @cmd end else begin if @Debugswitch > 0 begin print 'rolebased only not exists : ' + @XUser end insert into DialogUserInGroup ( UID_DialogGroup
32, UID_DialogUser, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, XObjectKey) select distinct G.UID_DialogGroup, U.UID_DialogUser, @XDate, @XDate
33, @XUser, @XUser , dbo.QBM_FCVElementToObjectKey2('DialogUserInGroup', 'UID_DialogGroup', G.UID_DialogGroup, 'UID_DialogUser', U.UID_DialogUser)  from 
34DialogUser U join DialogGroup G on 1=1 where u.IsAdmin = 1 and u.IsDynamicUser = 0 and u.IsServiceAccount = 0 and u.UID_DialogUser like @UID_DialogUserPattern
35 and not exists (select top 1 1 from DialogUserInGroup UiG where UiG.UID_DialogUser = U.UID_DialogUser and UiG.UID_DialogGroup = G.UID_DialogGroup ) end
36   if exists (select top 1 1 from QBM_VSchemaColumns c where c.TABLE_NAME = 'DialogGroup' and c.COLUMN_NAME = 'IsRoleBasedOnly' ) begin select @cmd = '
37    delete DialogUserInGroup 
38		    from DialogUserInGroup uig join DialogUser u on uig.UID_DialogUser = u.UID_DialogUser
39								    join DialogGroup g on uig.UID_DialogGroup = g.UID_DialogGroup
40		    where ((u.IsDynamicUser <> G.IsRoleBasedOnly 
41					)
42				  or u.IsServiceAccount = 1
43				  )
44				and u.UID_DialogUser like '''
45 + rtrim(@UID_DialogUserPattern) + '''
46		     and g.UID_DialogGroup <> ''' + @defaultGroup + '''		-- default rights
47		  ' exec sp_executesql @cmd end
48 else  begin delete DialogUserInGroup  from DialogUserInGroup uig join DialogUser u on uig.UID_DialogUser = u.UID_DialogUser join DialogGroup g on uig.UID_DialogGroup
49 = g.UID_DialogGroup where ( u.IsDynamicUser = 1 or u.IsServiceAccount = 1 ) and u.UID_DialogUser like rtrim(@UID_DialogUserPattern) and g.UID_DialogGroup
50 <> @defaultGroup  end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR
51 (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende: if @Debugswitch > 0 begin print 'exit : ' + @XUser end exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R
52 exec QBM_PSessionContextSet 'XUser', @XUser_R return end 
53