Back to OIM Explorer

dbo.QBM_ZDBRoleDefCreate

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 5.417 characters

Interpretation

Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FGIServerEngineProperties source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE PROCEDURE QBM_ZDBRoleDefCreate(2  @SlotNumber int,3  @UID_QBMDBRoleDef varchar(38),4  @Dummy varchar(38),5  @GenProcID varchar(38)6)7AS8BEGIN9  DECLARE @RoleName nvarchar(400) = NULL10  DECLARE @Scope nvarchar(16) = NULL11  DECLARE @SQLcmd nvarchar(max)12  DECLARE @msg nvarchar(max)13  DECLARE @ElementBuffer QBM_YCursorBuffer14  DECLARE @ElementCount int15  DECLARE @ElementIndex int16  DECLARE @LoginName nvarchar(256)17  DECLARE @UserName nvarchar(256)18  DECLARE @Operation varchar(30)19  BEGIN TRY20    SELECT21      TOP 1 @RoleName = r.RoleName,22      @Scope = r.Scope23    FROM QBMDBRoleDef r24    WHERE25      r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND EXISTS(26    SELECT TOP 1 127    FROM QBMDBPrincipalHasRoleDef phr28    JOIN QBMDBPrincipal m29      ON phr.UID_QBMDBPrincipal = m.UID_QBMDBPrincipal30    WHERE31      phr.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef AND m.LoginName > ' ')32    IF isnull(@RoleName,33    '') = ''34    BEGIN35      GOTO EndLabel36    END37    IF @Scope = 'Server' AND dbo.QBM_FGIServerEngineProperties('AccessToServerPrincipal') = 138    BEGIN39      SELECT @RoleName =40      LEFT(@RoleName + '_' + db_name(),41      128)42      IF NOT EXISTS(43        SELECT TOP 1 144        FROM sys.server_principals45        WHERE46          name = @RoleName AND type = 'R')47      BEGIN48        SELECT @SQLcmd = 'Create Server Role [' + @RoleName + ']'49        EXEC sp_executesql @SQLCmd50      END51      DELETE @ElementBuffer52      INSERT INTO @ElementBuffer(LongIdent1,53      Ident1)54      SELECT55        m.LoginName,56        'delete'57      FROM sys.server_role_members rm58      JOIN sys.server_principals rp59        ON rm.role_principal_id = rp.principal_id60      JOIN QBMDBRoleDef r61        ON62      LEFT(r.RoleName + '_' + db_name(),63      128) = rp.name Collate SQL_Latin1_General_CP1_CS_AS64      JOIN sys.server_principals mp65        ON rm.member_principal_id = mp.principal_id66      JOIN QBMDBPrincipal m67        ON m.LoginName = mp.name Collate SQL_Latin1_General_CP1_CS_AS68      WHERE69        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND rp.type = 'R' AND mp.type IN('S',70      'U',71      'G') AND NOT EXISTS(72      SELECT TOP 1 173      FROM QBMDBPrincipalHasRoleDef phr74      WHERE75        phr.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef AND phr.UID_QBMDBPrincipal = m.UID_QBMDBPrincipal)76      UNION77      SELECT78        m.LoginName,79        'insert'80      FROM QBMDBPrincipalHasRoleDef phr81      JOIN QBMDBRoleDef r82        ON phr.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef83      JOIN sys.server_principals rp84        ON85      LEFT(r.RoleName + '_' + db_name(),86      128) = rp.name Collate SQL_Latin1_General_CP1_CS_AS87      JOIN QBMDBPrincipal m88        ON phr.UID_QBMDBPrincipal = m.UID_QBMDBPrincipal89      JOIN sys.server_principals mp90        ON m.LoginName = mp.name Collate SQL_Latin1_General_CP1_CS_AS91      WHERE92        phr.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND rp.type = 'R' AND mp.type IN('S',93      'U',94      'G') AND NOT EXISTS(95      SELECT TOP 1 196      FROM sys.server_role_members rm97      WHERE98        rm.role_principal_id = rp.principal_id AND rm.member_principal_id = mp.principal_id)99      SELECT @ElementCount = count(*)100      FROM @ElementBuffer101      SELECT @ElementIndex = 1102      WHILE @ElementIndex <= @ElementCount103      BEGIN104        SELECT105          TOP 1 @LoginName = bu.LongIdent1,106          @Operation = bu.Ident1107        FROM @ElementBuffer bu108        WHERE109          bu.ElementIndex = @ElementIndex110        IF @Operation = 'delete'111        BEGIN112          SELECT113            @SQLcmd = 'Alter Server Role [' + @RoleName + '] Drop Member [' + @LoginName + ']'114          EXEC sp_executesql @SQLCmd115        END116        IF @Operation = 'insert'117        BEGIN118          SELECT119            @SQLcmd = 'Alter Server Role [' + @RoleName + '] Add Member [' + @LoginName + ']'120          EXEC sp_executesql @SQLCmd121        END122        SELECT @ElementIndex += 1123      END124    END125    IF @Scope = 'Database'126    BEGIN127      IF NOT EXISTS(128        SELECT TOP 1 1129        FROM sys.database_principals130        WHERE131          name = @RoleName AND type = 'R')132      BEGIN133        SELECT @SQLcmd = 'Create Role [' + @RoleName + ']'134        EXEC sp_executesql @SQLCmd135      END136      DELETE @ElementBuffer137      INSERT INTO @ElementBuffer(LongIdent1,138      Ident1)139      SELECT140        m.UserName,141        'delete'142      FROM sys.database_role_members rm143      JOIN sys.database_principals rp144        ON rm.role_principal_id = rp.principal_id145      JOIN QBMDBRoleDef r146        ON r.RoleName = rp.name147      JOIN sys.database_principals mp148        ON rm.member_principal_id = mp.principal_id149      JOIN QBMDBPrincipal m150        ON m.UserName = mp.name151      WHERE152        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND m.LoginName > ' ' AND rp.type = 'R' AND mp.type IN('S',153      'U',154      'G') AND NOT EXISTS(155      SELECT TOP 1 1156      FROM QBMDBPrincipalHasRoleDef phr157      WHERE158        phr.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef AND phr.UID_QBMDBPrincipal = m.UID_QBMDBPrincipal)159      UNION160      SELECT161        m.UserName,162        'insert'163      FROM QBMDBPrincipalHasRoleDef phr164      JOIN QBMDBRoleDef r165        ON phr.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef166      JOIN sys.database_principals rp167        ON r.RoleName = rp.name168      JOIN QBMDBPrincipal m169        ON phr.UID_QBMDBPrincipal = m.UID_QBMDBPrincipal170      JOIN sys.database_principals mp171        ON m.UserName = mp.name172      WHERE173        phr.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND m.LoginName > ' ' AND rp.type = 'R' AND mp.type IN('S',174      'U',175      'G') AND NOT EXISTS(176      SELECT TOP 1 1177      FROM sys.database_role_members rm178      WHERE179        rm.role_principal_id = rp.principal_id AND rm.member_principal_id = mp.principal_id)180      SELECT @ElementCount = count(*)181      FROM @ElementBuffer182      SELECT @ElementIndex = 1183      WHILE @ElementIndex <= @ElementCount184      BEGIN185        SELECT186          TOP 1 @UserName = bu.LongIdent1,187          @Operation = bu.Ident1188        FROM @ElementBuffer bu189        WHERE190          bu.ElementIndex = @ElementIndex191        IF @Operation = 'delete'192        BEGIN193          SELECT @SQLcmd = 'Alter Role [' + @RoleName + '] Drop Member [' + @UserName + ']'194          EXEC sp_executesql @SQLCmd195        END196        IF @Operation = 'insert'197        BEGIN198          SELECT @SQLcmd = 'Alter Role [' + @RoleName + '] Add Member [' + @UserName + ']'199          EXEC sp_executesql @SQLCmd200        END201        SELECT @ElementIndex += 1202      END203    END204    EndLabel:205    RETURN206  END TRY207  BEGIN CATCH208    EXEC QBM_PSessionErrorAdd DEFAULT209    RAISERROR('',210    18,211    1)212      WITH NOWAIT213  END CATCH214END

Open raw exported source

1  create   procedure QBM_ZDBRoleDefCreate (@SlotNumber int , @UID_QBMDBRoleDef varchar(38) , @Dummy varchar(38) , @GenProcID varchar(38) ) as begin2 declare @RoleName nvarchar(400) = null declare @Scope nvarchar(16) = null declare @SQLcmd nvarchar(max) declare @msg nvarchar(max) declare @ElementBuffer3 QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @LoginName nvarchar(256) declare @UserName nvarchar(256) declare @Operation4 varchar(30) BEGIN TRY select top 1 @RoleName = r.RoleName , @Scope = r.Scope from QBMDBRoleDef r where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and exists5 (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_FGIServerEngineProperties7('AccessToServerPrincipal') = 1  begin select @RoleName = left(@RoleName + '_' + db_name(), 128)  if not exists (select top 1 1 from sys.server_principals8 where name = @RoleName and type = 'R'  ) begin select @SQLcmd = 'Create Server Role [' + @RoleName + ']' exec sp_executesql @SQLCmd end  delete  @ElementBuffer9  insert into @ElementBuffer (LongIdent1, Ident1) select m.LoginName, 'delete' from sys.server_role_members rm join sys.server_principals rp on rm.role_principal_id10 = 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_principals11 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_QBMDBRoleDef12 = @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_QBMDBRoleDef13 = r.UID_QBMDBRoleDef and phr.UID_QBMDBPrincipal = m.UID_QBMDBPrincipal ) union select m.LoginName, 'insert' from QBMDBPrincipalHasRoleDef phr join QBMDBRoleDef14 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_AS15  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_AS16  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_members17 rm where rm.role_principal_id = rp.principal_id and rm.member_principal_id = mp.principal_id ) select @ElementCount = count(*) from @ElementBuffer select18 @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @LoginName = bu.LongIdent1 , @Operation = bu.Ident1 from @ElementBuffer bu where19 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_principals22 where name = @RoleName and type = 'R'  ) begin select @SQLcmd = 'Create Role [' + @RoleName + ']' exec sp_executesql @SQLCmd end  delete  @ElementBuffer23  insert into @ElementBuffer (LongIdent1, Ident1) select m.UserName, 'delete' from sys.database_role_members rm join sys.database_principals rp on rm.role_principal_id24 = 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 QBMDBPrincipal25 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_QBMDBPrincipal27 ) union select m.UserName, 'insert' from QBMDBPrincipalHasRoleDef phr join QBMDBRoleDef r on phr.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef join sys.database_principals28 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.name29 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 from30 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.Ident132 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 [' + @UserName34 + ']' exec sp_executesql @SQLCmd end select @ElementIndex += 1 end  end  EndLabel: return END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR35 ('', 18, 1) WITH NOWAIT END CATCH end 36