Back to OIM Explorer

dbo.QBM_ZDBRoleDefFill

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.898 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_FCVElementToObjectKey1 source text reference
references source dbo.QBM_FCVStringToGUID source text reference
references source dbo.QBM_FGIDBOwner source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE PROCEDURE QBM_ZDBRoleDefFill(2  @SlotNumber int,3  @Dummy1 varchar(38),4  @Dummy2 varchar(38),5  @GenProcID varchar(38)6)7AS8DECLARE @ElementBuffer QBM_YCursorBuffer9DECLARE @ElementCount int10DECLARE @ElementIndex int11DECLARE @SQLcmd nvarchar(max)12DECLARE @UID_QBMDBRoleDef varchar(38)13DECLARE @DBOwner varchar(3)14DECLARE @Schema_Id int15DECLARE @MyName nvarchar(64) = object_name(@@procid)16BEGIN17  BEGIN TRY18    SELECT @UID_QBMDBRoleDef = 'QBM-DBRole-OneIMEndUserRoleDB'19    SELECT TOP 1 @DBOwner = dbo.QBM_FGIDBOwner()20    IF isnull(@DBOwner,21    '') <> 'CCC'22    BEGIN23      GOTO EndLabel24    END25    DELETE QBMDBRightsAddOn26    WHERE27      UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND UID_QBMDBRightsAddOn LIKE 'CCC%' AND ObjectClass IN('VIEW',28    'FUNCTION',29    'TABLE_FUNCTION',30    'PROCEDURE',31    'USER_TABLE') AND NOT EXISTS(32    SELECT TOP 1 133    FROM sys.objects o34    WHERE35      o.name = QBMDBRightsAddOn.ObjectName)36    SELECT TOP 1 @Schema_Id = schema_id37    FROM sys.schemas38    WHERE39      name = 'dbo'40    INSERT INTO @ElementBuffer(Ident1,41    Ident2,42    Ident3,43    LongIdent1)44    SELECT45      'SELECT',46      'SL',47      'USER_TABLE',48      convert(nvarchar(64),49      o.name) AS Scriptname50    FROM sys.tables o51    JOIN DialogTable t52      ON convert(varchar(64),53    rtrim(o.name)) = t.TableName54    WHERE55      o.type = 'U' AND o.is_memory_optimized = 0 AND o.schema_id = @Schema_id AND56    LEFT(isnull(t.UID_DialogTable, '1234'),57    4) NOT IN(58    SELECT md.ModuleName + '-'59    FROM QBMModuleDef md60    WHERE61      md.ModuleName <> 'CCC') AND NOT EXISTS(62    SELECT TOP 1 163    FROM QBMDBRightsAddOn r64    WHERE65      r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND r.ObjectClass = 'USER_TABLE' AND r.ObjectName = o.name AND r.PermissionName = 'SELECT')66    UNION all67    SELECT68      'INSERT',69      'IN',70      'USER_TABLE',71      convert(nvarchar(64),72      o.name) AS Scriptname73    FROM sys.tables o74    JOIN DialogTable t75      ON convert(varchar(64),76    rtrim(o.name)) = t.TableName77    WHERE78      o.type = 'U' AND o.is_memory_optimized = 0 AND o.schema_id = @Schema_id AND79    LEFT(isnull(t.UID_DialogTable, '1234'),80    4) NOT IN(81    SELECT md.ModuleName + '-'82    FROM QBMModuleDef md83    WHERE84      md.ModuleName <> 'CCC') AND NOT EXISTS(85    SELECT TOP 1 186    FROM QBMDBRightsAddOn r87    WHERE88      r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND r.ObjectClass = 'USER_TABLE' AND r.ObjectName = o.name AND r.PermissionName = 'INSERT')89    UNION all90    SELECT91      'UPDATE',92      'UP',93      'USER_TABLE',94      convert(nvarchar(64),95      o.name) AS Scriptname96    FROM sys.tables o97    JOIN DialogTable t98      ON convert(varchar(64),99    rtrim(o.name)) = t.TableName100    WHERE101      o.type = 'U' AND o.is_memory_optimized = 0 AND o.schema_id = @Schema_id AND102    LEFT(isnull(t.UID_DialogTable, '1234'),103    4) NOT IN(104    SELECT md.ModuleName + '-'105    FROM QBMModuleDef md106    WHERE107      md.ModuleName <> 'CCC') AND NOT EXISTS(108    SELECT TOP 1 1109    FROM QBMDBRightsAddOn r110    WHERE111      r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND r.ObjectClass = 'USER_TABLE' AND r.ObjectName = o.name AND r.PermissionName = 'UPDATE')112    UNION all113    SELECT114      'DELETE',115      'DL',116      'USER_TABLE',117      convert(nvarchar(64),118      o.name) AS Scriptname119    FROM sys.tables o120    JOIN DialogTable t121      ON convert(varchar(64),122    rtrim(o.name)) = t.TableName123    WHERE124      o.type = 'U' AND o.is_memory_optimized = 0 AND o.schema_id = @Schema_id AND125    LEFT(isnull(t.UID_DialogTable, '1234'),126    4) NOT IN(127    SELECT md.ModuleName + '-'128    FROM QBMModuleDef md129    WHERE130      md.ModuleName <> 'CCC') AND NOT EXISTS(131    SELECT TOP 1 1132    FROM QBMDBRightsAddOn r133    WHERE134      r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND r.ObjectClass = 'USER_TABLE' AND r.ObjectName = o.name AND r.PermissionName = 'DELETE')135    UNION all136    SELECT137      'SELECT',138      'SL',139      'VIEW',140      convert(nvarchar(64),141      o.name)142    FROM sys.objects o143    JOIN DialogTable t144      ON convert(varchar(64),145    rtrim(o.name)) = t.TableName146    WHERE147      o.type = 'V' AND t.tabletype IN('V',148    'P',149    'U',150    'R') AND151    LEFT(t.UID_DialogTable,152    4) = 'CCC' + '-' AND o.name NOT LIKE 'sys%' AND o.schema_id = @Schema_id AND NOT EXISTS(153    SELECT TOP 1 1154    FROM QBMDBRightsAddOn r155    WHERE156      r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND r.ObjectClass = 'VIEW' AND r.ObjectName = convert(nvarchar(64),157    o.name) AND r.PermissionName = 'SELECT')158    UNION all159    SELECT160      'SELECT',161      'SL',162      'VIEW',163      convert(nvarchar(64),164      o.name)165    FROM sys.objects o166    WHERE167      o.type = 'V' AND NOT EXISTS(168    SELECT TOP 1 1169    FROM dialogtable t170    WHERE171      t.tabletype IN('V', 'P', 'U', 'R') AND t.TableName = convert(nvarchar(64), o.name)) AND172    LEFT(o.name,173    4) NOT IN(174    SELECT md.ModuleName + '_'175    FROM QBM_VQBMModuleDef md176    WHERE177      md.ModuleName <> 'CCC') AND o.name NOT LIKE 'sys%' AND o.schema_id = @Schema_id AND NOT EXISTS(178    SELECT TOP 1 1179    FROM QBMDBRightsAddOn r180    WHERE181      r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND r.ObjectClass = 'VIEW' AND r.ObjectName = convert(nvarchar(64),182    o.name) AND r.PermissionName = 'SELECT')183    UNION all184    SELECT185      'SELECT',186      'SL',187      'TABLE_FUNCTION',188      convert(nvarchar(64),189      o.name)190    FROM sys.objects o191    WHERE192      o.type IN('TF',193    'IF') AND194    LEFT(o.name,195    4) NOT IN(196    SELECT md.ModuleName + '_'197    FROM QBM_VQBMModuleDef md198    WHERE199      md.ModuleName <> 'CCC') AND o.name NOT LIKE 'sys%' AND o.schema_id = @Schema_id AND NOT EXISTS(200    SELECT TOP 1 1201    FROM QBMDBRightsAddOn r202    WHERE203      r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND r.ObjectClass = 'TABLE_FUNCTION' AND r.ObjectName = convert(nvarchar(64),204    o.name) AND r.PermissionName = 'SELECT')205    UNION all206    SELECT207      'EXECUTE',208      'EX',209    CASE210      WHEN o.type = 'P' THEN211    'PROCEDURE'212    ELSE 'FUNCTION'213    END,214    convert(nvarchar(64),215    o.name)216    FROM sys.objects o217    WHERE218      o.type IN('P',219    'FN') AND o.schema_id = @Schema_id AND220    LEFT(o.name,221    3) NOT IN('sp_',222    'ms_',223    'dt_') AND224    LEFT(o.name,225    4) NOT IN(226    SELECT md.ModuleName + '_'227    FROM QBM_VQBMModuleDef md228    WHERE229      md.ModuleName <> 'CCC') AND NOT EXISTS(230    SELECT TOP 1 1231    FROM QBMDBRightsAddOn r232    WHERE233      r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND r.ObjectClass = CASE234    WHEN o.type = 'P' THEN235    'PROCEDURE'236    ELSE 'FUNCTION'237    END AND r.ObjectName = convert(nvarchar(64), o.name) AND r.PermissionName = 'EXECUTE')238    SELECT @ElementCount = count(*)239    FROM @ElementBuffer240    SELECT @ElementIndex = 1241    WHILE @ElementIndex <= @ElementCount242    BEGIN243      SELECT244        TOP 1 @SQLcmd = 'insert into QBMDBRightsAddOn(UID_QBMDBRightsAddOn, UID_QBMDBRoleDef, PermissionName, PermissionType, ObjectClass, ObjectName245									, XObjectKey246									, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated)247								values (dbo.QBM_FCVStringToGUID('''248        + @DBOwner + ''', ''' + SUBSTRING(@UID_QBMDBRoleDef,249        12,250        38) + bu.Ident1 + bu.LongIdent1 + '''), ''' + @UID_QBMDBRoleDef + ''', ''' + bu.Ident1 + ''', ''' + bu.Ident2 + ''', ''' + bu.Ident3 + ''', '''251        + bu.LongIdent1 + '''252											, dbo.QBM_FCVElementToObjectKey1(''QBMDBRightsAddOn'', ''UID_QBMDBRightsAddOn'', dbo.QBM_FCVStringToGUID('''253        + @DBOwner + ''', ''' + SUBSTRING(@UID_QBMDBRoleDef,254        12,255        38) + bu.Ident1 + bu.LongIdent1 + '''))256											, GETUTCDATE(), GETUTCDATE(), ''' + @MyName + ''', ''' + @MyName + ''')'257      FROM @ElementBuffer bu258      WHERE259        bu.ElementIndex = @ElementIndex260      EXEC sp_executesql @SQLCmd261      SELECT @ElementIndex += 1262    END263    EndLabel:264    RETURN265  END TRY266  BEGIN CATCH267    EXEC QBM_PSessionErrorAdd DEFAULT268    RAISERROR('',269    18,270    1)271      WITH NOWAIT272  END CATCH273END

Open raw exported source

1  create   procedure QBM_ZDBRoleDefFill (@SlotNumber int , @Dummy1 varchar(38) , @Dummy2 varchar(38) , @GenProcID varchar(38) ) as declare @ElementBuffer2 QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @SQLcmd nvarchar(max) declare @UID_QBMDBRoleDef varchar(38) declare @DBOwner3 varchar(3) declare @Schema_Id int declare @MyName nvarchar(64) = object_name(@@procid) begin begin try select @UID_QBMDBRoleDef = 'QBM-DBRole-OneIMEndUserRoleDB'4 select top 1 @DBOwner = dbo.QBM_FGIDBOwner() if isnull(@DBOwner, '') <> 'CCC' begin goto EndLabel end  delete QBMDBRightsAddOn where UID_QBMDBRoleDef 5= @UID_QBMDBRoleDef and UID_QBMDBRightsAddOn like 'CCC%' and ObjectClass in ('VIEW', 'FUNCTION', 'TABLE_FUNCTION', 'PROCEDURE', 'USER_TABLE') and not exists6 (select top 1 1 from sys.objects o where o.name = QBMDBRightsAddOn.ObjectName ) select top 1 @Schema_Id = schema_id from sys.schemas where name = 'dbo'7 insert into @ElementBuffer (Ident1, Ident2, Ident3, LongIdent1)  select 'SELECT', 'SL', 'USER_TABLE', convert(nvarchar(64), o.name) as Scriptname from8 sys.tables o join DialogTable t on convert(varchar(64), rtrim(o.name)) = t.TableName where o.type = 'U' and o.is_memory_optimized = 0 and o.schema_id 9= @Schema_id and left(isnull(t.UID_DialogTable, '1234'), 4) not in (Select md.ModuleName + '-' from QBMModuleDef md where md.ModuleName <> 'CCC' ) and 10not exists (select top 1 1 from QBMDBRightsAddOn r where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and r.ObjectClass = 'USER_TABLE' and r.ObjectName = o.name11 and r.PermissionName = 'SELECT' ) union all  select 'INSERT', 'IN', 'USER_TABLE', convert(nvarchar(64), o.name) as Scriptname from sys.tables o join DialogTable12 t on convert(varchar(64), rtrim(o.name)) = t.TableName where o.type = 'U' and o.is_memory_optimized = 0 and o.schema_id = @Schema_id and left(isnull(t.UID_DialogTable13, '1234'), 4) not in (Select md.ModuleName + '-' from QBMModuleDef md where md.ModuleName <> 'CCC' ) and not exists (select top 1 1 from QBMDBRightsAddOn14 r where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and r.ObjectClass = 'USER_TABLE' and r.ObjectName = o.name and r.PermissionName = 'INSERT' ) union all 15 select 'UPDATE', 'UP', 'USER_TABLE', convert(nvarchar(64), o.name) as Scriptname from sys.tables o join DialogTable t on convert(varchar(64), rtrim(o.name16)) = t.TableName where o.type = 'U' and o.is_memory_optimized = 0 and o.schema_id = @Schema_id and left(isnull(t.UID_DialogTable, '1234'), 4) not in (Select17 md.ModuleName + '-' from QBMModuleDef md where md.ModuleName <> 'CCC' ) and not exists (select top 1 1 from QBMDBRightsAddOn r where r.UID_QBMDBRoleDef18 = @UID_QBMDBRoleDef and r.ObjectClass = 'USER_TABLE' and r.ObjectName = o.name and r.PermissionName = 'UPDATE' ) union all  select 'DELETE', 'DL', 'USER_TABLE'19, convert(nvarchar(64), o.name) as Scriptname from sys.tables o join DialogTable t on convert(varchar(64), rtrim(o.name)) = t.TableName where o.type = 20'U' and o.is_memory_optimized = 0 and o.schema_id = @Schema_id and left(isnull(t.UID_DialogTable, '1234'), 4) not in (Select md.ModuleName + '-' from QBMModuleDef21 md where md.ModuleName <> 'CCC' ) and not exists (select top 1 1 from QBMDBRightsAddOn r where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and r.ObjectClass22 = 'USER_TABLE' and r.ObjectName = o.name and r.PermissionName = 'DELETE' ) union all  select 'SELECT', 'SL', 'VIEW', convert(nvarchar(64), o.name) from23 sys.objects o join DialogTable t on convert(varchar(64), rtrim(o.name)) = t.TableName where o.type = 'V' and t.tabletype in ('V', 'P', 'U', 'R') and LEFT24(t.UID_DialogTable, 4) = 'CCC' + '-' and o.name not like 'sys%' and o.schema_id = @Schema_id and not exists (select top 1 1 from QBMDBRightsAddOn r where25 r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and r.ObjectClass = 'VIEW' and r.ObjectName = convert(nvarchar(64), o.name) and r.PermissionName = 'SELECT' ) union26 all  select 'SELECT', 'SL', 'VIEW', convert(nvarchar(64), o.name) from sys.objects o where o.type = 'V' and not exists (select top 1 1 from dialogtable27 t where t.tabletype in ( 'V', 'P', 'U', 'R') and t.TableName = convert(nvarchar(64), o.name) ) and LEFT(o.name, 4) not in (Select md.ModuleName + '_' 28from QBM_VQBMModuleDef md where md.ModuleName <> 'CCC' ) and o.name not like 'sys%' and o.schema_id = @Schema_id and not exists (select top 1 1 from QBMDBRightsAddOn29 r where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and r.ObjectClass = 'VIEW' and r.ObjectName = convert(nvarchar(64), o.name) and r.PermissionName = 'SELECT'30 ) union all  select 'SELECT', 'SL', 'TABLE_FUNCTION', convert(nvarchar(64), o.name) from sys.objects o where o.type in ('TF', 'IF') and LEFT(o.name, 431) not in (Select md.ModuleName + '_' from QBM_VQBMModuleDef md where md.ModuleName <> 'CCC' ) and o.name not like 'sys%' and o.schema_id = @Schema_id and32 not exists (select top 1 1 from QBMDBRightsAddOn r where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and r.ObjectClass = 'TABLE_FUNCTION' and r.ObjectName 33= convert(nvarchar(64), o.name) and r.PermissionName = 'SELECT' ) union all  select 'EXECUTE', 'EX', case when o.type = 'P' then 'PROCEDURE' else 'FUNCTION'34 end, convert(nvarchar(64), o.name) from sys.objects o where o.type in ('P', 'FN') and o.schema_id = @Schema_id and left(o.name, 3) not in ('sp_', 'ms_'35, 'dt_') and LEFT(o.name, 4) not in (Select md.ModuleName + '_' from QBM_VQBMModuleDef md where md.ModuleName <> 'CCC' ) and not exists (select top 1 136 from QBMDBRightsAddOn r where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and r.ObjectClass = case when o.type = 'P' then 'PROCEDURE' else 'FUNCTION' end and37 r.ObjectName = convert(nvarchar(64), o.name) and r.PermissionName = 'EXECUTE' ) select @ElementCount = count(*) from @ElementBuffer select @ElementIndex38 = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLcmd = 'insert into QBMDBRightsAddOn(UID_QBMDBRightsAddOn, UID_QBMDBRoleDef, PermissionName, PermissionType, ObjectClass, ObjectName39									, XObjectKey40									, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated)41								values (dbo.QBM_FCVStringToGUID('''42 + @DBOwner + ''', ''' + SUBSTRING(@UID_QBMDBRoleDef, 12, 38) + bu.Ident1 + bu.LongIdent1 + '''), ''' + @UID_QBMDBRoleDef + ''', ''' + bu.Ident1 + ''', '''43 + bu.Ident2 + ''', ''' + bu.Ident3 + ''', ''' + bu.LongIdent1 + '''44											, dbo.QBM_FCVElementToObjectKey1(''QBMDBRightsAddOn'', ''UID_QBMDBRightsAddOn'', dbo.QBM_FCVStringToGUID('''45 + @DBOwner + ''', ''' + SUBSTRING(@UID_QBMDBRoleDef, 12, 38) + bu.Ident1 + bu.LongIdent1 + '''))46											, GETUTCDATE(), GETUTCDATE(), ''' + @MyName47 + ''', ''' + @MyName + ''')' from @ElementBuffer bu where bu.ElementIndex = @ElementIndex exec sp_executesql @SQLCmd select @ElementIndex += 1 end  EndLabel:48 return END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 49