Back to OIM Explorer

dbo.QBM_ZDBPrincipalCreate

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QBM-K-DBRoleDefCreate / QBM_ZDBRoleDefCreate at line 18; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 2.896 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Bulk DBQueue insert -> QBM-K-DBRoleDefCreate / QBM_ZDBRoleDefCreate at line 18
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QBM_ZDBRoleDefCreate at line 18 Bulk DBQueue insert -> QBM-K-DBRoleDefCreate / QBM_ZDBRoleDefCreate at line 18
  • references source dbo.QBM_FGIServerEngineProperties source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task QBM-K-DBRoleDefCreate -> QBM_ZDBRoleDefCreate QBM_PDBQueueInsert_Bulk 'QBM-K-DBRoleDefCreate', @DBQueueElements_01 EndLabel: return END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL136 lines
1CREATE PROCEDURE QBM_ZDBPrincipalCreate(2  @SlotNumber int,3  @UID_QBMDBPrincipal varchar(38),4  @Dummy varchar(38),5  @GenProcID varchar(38)6)7AS8BEGIN9  DECLARE @LoginName nvarchar(128) = NULL10  DECLARE @UserName nvarchar(128) = NULL11  DECLARE @SQLcmd nvarchar(max)12  DECLARE @msg nvarchar(max)13  BEGIN TRY14    SELECT15      TOP 1 @LoginName = LoginName,16      @UserName = UserName17    FROM QBMDBPrincipal18    WHERE19      UID_QBMDBPrincipal = @UID_QBMDBPrincipal20    IF isnull(@LoginName,21    '') = ''22    BEGIN23      IF EXISTS(24        SELECT TOP 1 125        FROM sys.database_principals26        WHERE27          name = @UserName AND type IN('S', 'U', 'G'))28      BEGIN29        SELECT @SQLcmd = 'Drop User [' + @UserName + ']'30        EXEC sp_executesql @SQLCmd31      END32      GOTO EndLabel33    END34    IF dbo.QBM_FGIServerEngineProperties('AccessToServerPrincipal') = 135    BEGIN36      IF NOT EXISTS(37        SELECT TOP 1 138        FROM sys.server_principals39        WHERE40          name = @LoginName AND type IN('S', 'U', 'G'))41      BEGIN42        SELECT @msg = '#LDS#ServerLogin does not exist {0}.|' + @LoginName + N '|'43        RAISERROR(@msg,44        18,45        2)46          WITH nowait47      END48      ELSE49      BEGIN50        IF NOT EXISTS(51          SELECT TOP 1 152          FROM sys.database_principals53          WHERE54            name = @UserName AND type IN('S', 'U', 'G'))55        BEGIN56          SELECT57            @SQLcmd = 'Create User [' + @UserName + '] For Login [' + @LoginName + '] With Default_Schema=[dbo]'58          EXEC sp_executesql @SQLCmd59        END60        ELSE61        BEGIN62          IF NOT EXISTS(63            SELECT TOP 1 164            FROM sys.server_principals l65            JOIN sys.database_principals u66              ON l.sid = u.sid67            WHERE68              l.name = @LoginName AND l.type IN('S', 'U', 'G') AND u.name = @UserName AND u.type IN('S', 'U',69          'G'))70          BEGIN71            IF EXISTS(72              SELECT TOP 1 173              FROM sys.server_principals l74              JOIN sys.database_principals u75                ON l.type = u.type76              WHERE77                l.name = @LoginName AND u.name = @UserName)78            BEGIN79              SELECT @SQLcmd = 'Alter User [' + @UserName + '] With Login=[' + @LoginName + ']'80              EXEC sp_executesql @SQLCmd81            END82            ELSE83            BEGIN84              SELECT @SQLcmd = 'Drop User [' + @UserName + ']'85              EXEC sp_executesql @SQLCmd86              SELECT87                @SQLcmd = 'Create User [' + @UserName + '] For Login [' + @LoginName + '] With Default_Schema=[dbo]'88              EXEC sp_executesql @SQLCmd89            END90          END91        END92      END93    END94    ELSE95    BEGIN96      IF NOT EXISTS(97        SELECT TOP 1 198        FROM sys.database_principals99        WHERE100          name = @UserName AND type IN('S', 'U', 'G'))101      BEGIN102        SELECT103          @SQLcmd = 'Create User [' + @UserName + '] For Login [' + @LoginName + '] With Default_Schema=[dbo]'104        EXEC sp_executesql @SQLCmd105      END106      ELSE107      BEGIN108        SELECT @SQLcmd = 'Alter User [' + @UserName + '] With Login=[' + @LoginName + ']'109        EXEC sp_executesql @SQLCmd110      END111    END112    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw113    INSERT INTO @DBQueueElements_01(object,114    subobject,115    genprocid)116    SELECT117      x.uid,118      NULL,119      @GenProcID120    FROM(121    SELECT UID_QBMDBRoleDef AS UID122    FROM QBMDBPrincipalHasRoleDef123    WHERE124      UID_QBMDBPrincipal = @UID_QBMDBPrincipal) AS x125    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-DBRoleDefCreate',126      @DBQueueElements_01 EndLabel:127    RETURN128  END TRY129  BEGIN CATCH130    EXEC QBM_PSessionErrorAdd DEFAULT131    RAISERROR('',132    18,133    1)134      WITH NOWAIT135  END CATCH136END
Open raw exported source
SQL ยท Raw20 lines
1  create   procedure QBM_ZDBPrincipalCreate (@SlotNumber int , @UID_QBMDBPrincipal varchar(38) , @Dummy varchar(38) , @GenProcID varchar(38) ) as2 begin declare @LoginName nvarchar(128) = null declare @UserName nvarchar(128) = null declare @SQLcmd nvarchar(max) declare @msg nvarchar(max) BEGIN TRY3 select top 1 @LoginName = LoginName , @UserName = UserName from QBMDBPrincipal where UID_QBMDBPrincipal = @UID_QBMDBPrincipal if isnull(@LoginName, ''4) = ''  begin if exists (select top 1 1 from sys.database_principals where name = @UserName and type in ('S', 'U', 'G') ) begin select @SQLcmd = 'Drop User ['5 + @UserName + ']' exec sp_executesql @SQLCmd end goto EndLabel end if dbo.QBM_FGIServerEngineProperties('AccessToServerPrincipal') = 1  begin if not exists6 (select top 1 1 from sys.server_principals where name = @LoginName and type in ('S'  , 'U'  , 'G')  ) begin select @msg = '#LDS#ServerLogin does not exist {0}.|'7 + @LoginName + N'|' raiserror(@msg, 18, 2) with nowait end else begin if not exists (select top 1 1 from sys.database_principals where name = @UserName8 and type in ('S', 'U', 'G') ) begin  select @SQLcmd = 'Create User [' + @UserName + '] For Login [' + @LoginName + '] With Default_Schema=[dbo]' exec 9sp_executesql @SQLCmd end else begin  if not exists (select top 1 1 from sys.server_principals l join sys.database_principals u on l.sid = u.sid where 10l.name = @LoginName and l.type in ('S', 'U', 'G') and u.name = @UserName and u.type in ('S', 'U', 'G') ) begin  if exists (select top 1 1 from sys.server_principals11 l join sys.database_principals u on l.type = u.type where l.name = @LoginName and u.name = @UserName ) begin select @SQLcmd = 'Alter User [' + @UserName12 + '] With Login=[' + @LoginName + ']' exec sp_executesql @SQLCmd end else begin  select @SQLcmd = 'Drop User [' + @UserName + ']' exec sp_executesql @SQLCmd13 select @SQLcmd = 'Create User [' + @UserName + '] For Login [' + @LoginName + '] With Default_Schema=[dbo]' exec sp_executesql @SQLCmd end end end end14 end else begin if not exists (select top 1 1 from sys.database_principals where name = @UserName and type in ('S', 'U', 'G') ) begin  select @SQLcmd =15 'Create User [' + @UserName + '] For Login [' + @LoginName + '] With Default_Schema=[dbo]' exec sp_executesql @SQLCmd end else begin select @SQLcmd = 16'Alter User [' + @UserName + '] With Login=[' + @LoginName + ']' exec sp_executesql @SQLCmd end end  declare @DBQueueElements_01 QBM_YDBQueueRaw insert17 into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select UID_QBMDBRoleDef as UID from QBMDBPrincipalHasRoleDef18 where UID_QBMDBPrincipal = @UID_QBMDBPrincipal ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-DBRoleDefCreate', @DBQueueElements_01 EndLabel: return END TRY19 BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 20