dbo.QBM_ZDBRoleDefCreate

1  create   procedure QBM_ZDBRoleDefCreate (@SlotNumber int , @UID_QBMDBRoleDef varchar(38) , @Dummy varchar(38) , @GenProcID varchar(38) ) as begin
2 declare @RoleName nvarchar(400) = null declare @Scope nvarchar(16) = null declare @SQLcmd nvarchar(max) declare @msg nvarchar(max) declare @ElementBuffer
3 QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @LoginName nvarchar(256) declare @UserName nvarchar(256) declare @Operation
4 varchar(30) BEGIN TRY select top 1 @RoleName = r.RoleName , @Scope = r.Scope from QBMDBRoleDef r where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and exists
5 (select top 1 1 from QBMDBPrincipalHasRoleDef phr join QBMDBPrincipal m on phr.UID_QBMDBPrincipal = m.UID_QBMDBPrincipal where phr.UID_QBMDBRoleDef = 
6r.UID_QBMDBRoleDef and m.LoginName > ' ' ) if isnull(@RoleName, '') = ''  begin goto EndLabel end if @Scope = 'Server' and dbo.QBM_FGIServerEngineProperties
7('AccessToServerPrincipal') = 1  begin select @RoleName = left(@RoleName + '_' + db_name(), 128)  if not exists (select top 1 1 from sys.server_principals
8 where name = @RoleName and type = 'R'  ) begin select @SQLcmd = 'Create Server Role [' + @RoleName + ']' exec sp_executesql @SQLCmd end  delete  @ElementBuffer
9  insert into @ElementBuffer (LongIdent1, Ident1) select m.LoginName, 'delete' from sys.server_role_members rm join sys.server_principals rp on rm.role_principal_id
10 = rp.principal_id join QBMDBRoleDef r on left(r.RoleName + '_' + db_name(), 128) = rp.name Collate SQL_Latin1_General_CP1_CS_AS  join sys.server_principals
11 mp on rm.member_principal_id = mp.principal_id join QBMDBPrincipal m on m.LoginName = mp.name Collate SQL_Latin1_General_CP1_CS_AS  where r.UID_QBMDBRoleDef
12 = @UID_QBMDBRoleDef and rp.type = 'R' and mp.type in ('S', 'U', 'G') and not exists (select top 1 1 from QBMDBPrincipalHasRoleDef phr where phr.UID_QBMDBRoleDef
13 = r.UID_QBMDBRoleDef and phr.UID_QBMDBPrincipal = m.UID_QBMDBPrincipal ) union select m.LoginName, 'insert' from QBMDBPrincipalHasRoleDef phr join QBMDBRoleDef
14 r on phr.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef join sys.server_principals rp on left(r.RoleName + '_' + db_name(), 128) = rp.name Collate SQL_Latin1_General_CP1_CS_AS
15  join QBMDBPrincipal m on phr.UID_QBMDBPrincipal = m.UID_QBMDBPrincipal join sys.server_principals mp on m.LoginName = mp.name Collate SQL_Latin1_General_CP1_CS_AS
16  where phr.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and rp.type = 'R' and mp.type in ('S', 'U', 'G') and not exists (select top 1 1 from sys.server_role_members
17 rm where rm.role_principal_id = rp.principal_id and rm.member_principal_id = mp.principal_id ) select @ElementCount = count(*) from @ElementBuffer select
18 @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @LoginName = bu.LongIdent1 , @Operation = bu.Ident1 from @ElementBuffer bu where
19 bu.ElementIndex = @ElementIndex if @Operation = 'delete' begin select @SQLcmd = 'Alter Server Role [' + @RoleName + '] Drop Member [' + @LoginName + ']'
20 exec sp_executesql @SQLCmd end if @Operation = 'insert' begin select @SQLcmd = 'Alter Server Role [' + @RoleName + '] Add Member [' + @LoginName + ']'
21 exec sp_executesql @SQLCmd end select @ElementIndex += 1 end  end  if @Scope = 'Database' begin if not exists (select top 1 1 from sys.database_principals
22 where name = @RoleName and type = 'R'  ) begin select @SQLcmd = 'Create Role [' + @RoleName + ']' exec sp_executesql @SQLCmd end  delete  @ElementBuffer
23  insert into @ElementBuffer (LongIdent1, Ident1) select m.UserName, 'delete' from sys.database_role_members rm join sys.database_principals rp on rm.role_principal_id
24 = rp.principal_id join QBMDBRoleDef r on r.RoleName = rp.name join sys.database_principals mp on rm.member_principal_id = mp.principal_id join QBMDBPrincipal
25 m on m.UserName = mp.name where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and m.LoginName > ' ' and rp.type = 'R' and mp.type in ('S', 'U', 'G') and not 
26exists (select top 1 1 from QBMDBPrincipalHasRoleDef phr where phr.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef and phr.UID_QBMDBPrincipal = m.UID_QBMDBPrincipal
27 ) union select m.UserName, 'insert' from QBMDBPrincipalHasRoleDef phr join QBMDBRoleDef r on phr.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef join sys.database_principals
28 rp on r.RoleName = rp.name join QBMDBPrincipal m on phr.UID_QBMDBPrincipal = m.UID_QBMDBPrincipal join sys.database_principals mp on m.UserName = mp.name
29 where phr.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and m.LoginName > ' ' and rp.type = 'R' and mp.type in ('S', 'U', 'G') and not exists (select top 1 1 from
30 sys.database_role_members rm where rm.role_principal_id = rp.principal_id and rm.member_principal_id = mp.principal_id ) select @ElementCount = count(*
31) from @ElementBuffer select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @UserName = bu.LongIdent1 , @Operation = bu.Ident1
32 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @Operation = 'delete' begin select @SQLcmd = 'Alter Role [' + @RoleName + '] Drop Member ['
33 + @UserName + ']' exec sp_executesql @SQLCmd end if @Operation = 'insert' begin select @SQLcmd = 'Alter Role [' + @RoleName + '] Add Member [' + @UserName
34 + ']' exec sp_executesql @SQLCmd end select @ElementIndex += 1 end  end  EndLabel: return END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR
35 ('', 18, 1) WITH NOWAIT END CATCH end 
36