Back to OIM Explorer

dbo.QBM_ZDBRoleDefPropagate

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 10.065 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_ZDBRoleDefPropagate(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 @master sysname = 'master.sys.sp_executesql'14  DECLARE @ElementBuffer QBM_YCursorBuffer15  DECLARE @ElementCount int16  DECLARE @ElementIndex int17  DECLARE @DatabaseName nvarchar(128)18  BEGIN TRY19    SELECT @DatabaseName = db_name()20    SELECT21      TOP 1 @RoleName = rd.RoleName,22      @Scope = rd.Scope23    FROM QBMDBRoleDef rd24    JOIN(25    SELECT26      'Database' AS Scope,27      name AS RoleName28    FROM sys.database_principals29    WHERE30      type = 'R'31    UNION all32    SELECT33      'Server',34      name35    FROM sys.server_principals36    WHERE37      type = 'R') r38      ON r.Scope = rd.Scope AND r.Rolename = CASE r.Scope39    WHEN 'Database' THEN40    rd.RoleName Collate SQL_Latin1_General_CP1_CS_AS41    ELSE42    LEFT(rd.Rolename + '_' + @DatabaseName,43    128) Collate SQL_Latin1_General_CP1_CS_AS44    END45    WHERE46      rd.UID_QBMDBRoleDef = @UID_QBMDBRoleDef47    IF isnull(@RoleName,48    '') = ''49    BEGIN50      GOTO EndLabel51    END52    IF @Scope = 'Database'53    BEGIN54      DELETE @ElementBuffer55      INSERT INTO @ElementBuffer(Ident1,56      ContentFull)57      SELECT58        'revoke',59        'Revoke ' + p.permission_name Collate SQL_Latin1_General_CP1_CS_AS + ' ON DATABASE::' + @DatabaseName + ' FROM ' + r.RoleName60      FROM sys.database_principals rp61      JOIN QBMDBRoleDef r62        ON r.RoleName = rp.name63      JOIN sys.database_permissions p64        ON rp.principal_id = p.grantee_principal_id65      WHERE66        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND rp.type = 'R' AND p.state = 'G' AND p.class_desc = 'DATABASE' AND NOT EXISTS(67      SELECT TOP 1 168      FROM QBMDBRightsAddOn a69      WHERE70        a.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef AND a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS AND a.ObjectClass71      = 'DATABASE')72      UNION all73      SELECT74        'revoke',75        'Revoke ' + p.permission_name Collate SQL_Latin1_General_CP1_CS_AS + ' ON OBJECT::' + o.name + ' FROM ' + r.RoleName76      FROM sys.database_principals rp77      JOIN QBMDBRoleDef r78        ON r.RoleName = rp.name79      JOIN sys.database_permissions p80        ON rp.principal_id = p.grantee_principal_id81      JOIN sys.objects o82        ON p.major_id = o.object_id83      WHERE84        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND rp.type = 'R' AND p.state = 'G' AND o.type <> 'S' AND NOT EXISTS(85      SELECT TOP 1 186      FROM QBMDBRightsAddOn a87      WHERE88        a.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef AND a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS AND a.ObjectName =89      o.name)90      UNION all91      SELECT92        'revoke',93        'Revoke ' + p.permission_name Collate SQL_Latin1_General_CP1_CS_AS + ' ON TYPE::' + o.name + ' FROM ' + r.RoleName94      FROM sys.database_principals rp95      JOIN QBMDBRoleDef r96        ON r.RoleName = rp.name97      JOIN sys.database_permissions p98        ON rp.principal_id = p.grantee_principal_id99      JOIN sys.table_types o100        ON p.major_id = o.user_type_id101      WHERE102        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND rp.type = 'R' AND p.state = 'G' AND p.class_desc = 'TYPE' AND NOT EXISTS(103      SELECT TOP 1 1104      FROM QBMDBRightsAddOn a105      WHERE106        a.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef AND a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS AND a.ObjectName =107      o.name AND a.ObjectClass = 'TYPE')108      UNION all109      SELECT110        'revoke',111        'Revoke ' + p.permission_name Collate SQL_Latin1_General_CP1_CS_AS + ' ON SCHEMA::"' + o.name + '" FROM ' + r.RoleName112      FROM sys.database_principals rp113      JOIN QBMDBRoleDef r114        ON r.RoleName = rp.name115      JOIN sys.database_permissions p116        ON rp.principal_id = p.grantee_principal_id117      JOIN sys.schemas o118        ON p.major_id = o.schema_id119      WHERE120        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND rp.type = 'R' AND p.state = 'G' AND p.class_desc = 'SCHEMA' AND NOT EXISTS(121      SELECT TOP 1 1122      FROM QBMDBRightsAddOn a123      WHERE124        a.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef AND a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS AND a.ObjectName =125      o.name AND a.ObjectClass = 'SCHEMA')126      UNION all127      SELECT128        'revoke',129        'Revoke ' + p.permission_name Collate SQL_Latin1_General_CP1_CS_AS + ' ON USER::' + o.name + ' FROM ' + r.RoleName130      FROM sys.database_principals rp131      JOIN QBMDBRoleDef r132        ON r.RoleName = rp.name133      JOIN sys.database_permissions p134        ON rp.principal_id = p.grantee_principal_id135      JOIN sys.database_principals o136        ON p.major_id = o.principal_id137      WHERE138        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND rp.type = 'R' AND p.state = 'G' AND p.class_desc = 'DATABASE_PRINCIPAL' AND NOT EXISTS(139      SELECT TOP 1 1140      FROM QBMDBRightsAddOn a141      WHERE142        a.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef AND a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS AND a.ObjectName =143      o.name AND a.ObjectClass = 'USER')144      UNION all145      SELECT146        'grant',147        'Grant ' + a.PermissionName + ' ON DATABASE::' + @DatabaseName + ' TO ' + r.RoleName148      FROM QBMDBRightsAddOn a149      JOIN QBMDBRoleDef r150        ON r.UID_QBMDBRoleDef = a.UID_QBMDBRoleDef151      WHERE152        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND a.ObjectClass = 'DATABASE' AND NOT EXISTS(153      SELECT TOP 1 1154      FROM sys.database_principals rp155      JOIN sys.database_permissions p156        ON rp.principal_id = p.grantee_principal_id157      WHERE158        rp.type = 'R' AND p.state = 'G' AND a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS AND p.class_desc = a.ObjectClass159      Collate SQL_Latin1_General_CP1_CS_AS AND rp.name = r.Rolename)160      UNION all161      SELECT162        'grant',163        'Grant ' + a.PermissionName + ' ON OBJECT::' + a.ObjectName + ' TO ' + r.RoleName164      FROM QBMDBRightsAddOn a165      JOIN QBMDBRoleDef r166        ON r.UID_QBMDBRoleDef = a.UID_QBMDBRoleDef167      JOIN sys.objects o168        ON a.ObjectName = o.name169      WHERE170        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND NOT EXISTS(171      SELECT TOP 1 1172      FROM sys.database_principals rp173      JOIN sys.database_permissions p174        ON rp.principal_id = p.grantee_principal_id175      WHERE176        rp.type = 'R' AND p.state = 'G' AND o.type <> 'S' AND a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS AND rp.name177      = r.Rolename AND p.major_id = o.object_id)178      UNION all179      SELECT180        'grant',181        'Grant ' + a.PermissionName + ' ON TYPE::' + a.ObjectName + ' TO ' + r.RoleName182      FROM QBMDBRightsAddOn a183      JOIN QBMDBRoleDef r184        ON r.UID_QBMDBRoleDef = a.UID_QBMDBRoleDef185      JOIN sys.table_types o186        ON a.ObjectName = o.name187      WHERE188        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND a.ObjectClass = 'TYPE' AND NOT EXISTS(189      SELECT TOP 1 1190      FROM sys.database_principals rp191      JOIN sys.database_permissions p192        ON rp.principal_id = p.grantee_principal_id193      WHERE194        rp.type = 'R' AND p.state = 'G' AND a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS AND rp.name = r.Rolename AND195      p.major_id = o.user_type_id)196      UNION all197      SELECT198        'grant',199        'Grant ' + a.PermissionName + ' ON SCHEMA::"' + a.ObjectName + '" TO ' + r.RoleName200      FROM QBMDBRightsAddOn a201      JOIN QBMDBRoleDef r202        ON r.UID_QBMDBRoleDef = a.UID_QBMDBRoleDef203      JOIN sys.schemas o204        ON a.ObjectName = o.name205      WHERE206        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND a.ObjectClass = 'SCHEMA' AND NOT EXISTS(207      SELECT TOP 1 1208      FROM sys.database_principals rp209      JOIN sys.database_permissions p210        ON rp.principal_id = p.grantee_principal_id211      WHERE212        rp.type = 'R' AND p.state = 'G' AND a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS AND rp.name = r.Rolename AND213      p.major_id = o.schema_id)214      UNION all215      SELECT216        'grant',217        'Grant ' + a.PermissionName + ' ON USER::' + a.ObjectName + ' TO ' + r.RoleName218      FROM QBMDBRightsAddOn a219      JOIN QBMDBRoleDef r220        ON r.UID_QBMDBRoleDef = a.UID_QBMDBRoleDef221      JOIN sys.database_principals o222        ON a.ObjectName = o.name223      WHERE224        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND a.ObjectClass = 'USER' AND NOT EXISTS(225      SELECT TOP 1 1226      FROM sys.database_principals rp227      JOIN sys.database_permissions p228        ON rp.principal_id = p.grantee_principal_id229      WHERE230        rp.type = 'R' AND p.state = 'G' AND a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS AND rp.name = r.Rolename AND231      p.major_id = o.principal_id)232      SELECT @ElementCount = count(*)233      FROM @ElementBuffer234      SELECT @ElementIndex = 1235      WHILE @ElementIndex <= @ElementCount236      BEGIN237        SELECT TOP 1 @SQLcmd = bu.ContentFull238        FROM @ElementBuffer bu239        WHERE240          bu.ElementIndex = @ElementIndex241        EXEC sp_executesql @SQLCmd242        SELECT @ElementIndex += 1243      END244    END245    IF @Scope = 'Server' AND dbo.QBM_FGIServerEngineProperties('AccessToServerPrincipal') = 1246    BEGIN247      DELETE @ElementBuffer248      INSERT INTO @ElementBuffer(Ident1,249      ContentFull)250      SELECT251        'revoke',252        'Revoke ' + p.permission_name Collate SQL_Latin1_General_CP1_CS_AS + ' FROM ' +253        LEFT(r.RoleName + '_' + @DatabaseName,254        128)255      FROM sys.server_principals rp256      JOIN QBMDBRoleDef r257        ON258      LEFT(r.RoleName + '_' + @DatabaseName,259      128) = rp.name Collate SQL_Latin1_General_CP1_CS_AS260      JOIN sys.server_permissions p261        ON rp.principal_id = p.grantee_principal_id262      WHERE263        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND rp.type = 'R' AND p.state = 'G' AND NOT EXISTS(264      SELECT TOP 1 1265      FROM QBMDBRightsAddOn a266      WHERE267        a.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef AND a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS AND p.class_desc =268      a.ObjectClass Collate SQL_Latin1_General_CP1_CS_AS AND a.ObjectClass = 'SERVER')269      UNION270      SELECT271        'grant',272        'Grant ' + a.PermissionName + ' TO ' +273        LEFT(r.RoleName + '_' + @DatabaseName,274        128)275      FROM QBMDBRightsAddOn a276      JOIN QBMDBRoleDef r277        ON r.UID_QBMDBRoleDef = a.UID_QBMDBRoleDef278      WHERE279        r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef AND a.ObjectClass = 'SERVER' AND NOT EXISTS(280      SELECT TOP 1 1281      FROM sys.server_principals rp282      JOIN sys.server_permissions p283        ON rp.principal_id = p.grantee_principal_id284      WHERE285        rp.type = 'R' AND p.state = 'G' AND a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS AND p.class_desc = a.ObjectClass286      Collate SQL_Latin1_General_CP1_CS_AS AND287      LEFT(r.RoleName + '_' + @DatabaseName, 128) = rp.name Collate SQL_Latin1_General_CP1_CS_AS)288      SELECT @ElementCount = count(*)289      FROM @ElementBuffer290      SELECT @ElementIndex = 1291      WHILE @ElementIndex <= @ElementCount292      BEGIN293        SELECT TOP 1 @SQLcmd = bu.ContentFull294        FROM @ElementBuffer bu295        WHERE296          bu.ElementIndex = @ElementIndex297        EXEC @master @SQLCmd298        SELECT @ElementIndex += 1299      END300    END301    EndLabel:302    RETURN303  END TRY304  BEGIN CATCH305    EXEC QBM_PSessionErrorAdd DEFAULT306    RAISERROR('',307    18,308    1)309      WITH NOWAIT310  END CATCH311END

Open raw exported source

1  create   procedure QBM_ZDBRoleDefPropagate (@SlotNumber int , @UID_QBMDBRoleDef varchar(38) , @Dummy varchar(38) , @GenProcID varchar(38) ) as2 begin declare @RoleName nvarchar(400) = null declare @Scope nvarchar(16) = null declare @SQLcmd nvarchar(max) declare @msg nvarchar(max) declare @master3 sysname = 'master.sys.sp_executesql' declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DatabaseName4 nvarchar(128) BEGIN TRY select @DatabaseName = db_name() select top 1 @RoleName = rd.RoleName , @Scope = rd.Scope from QBMDBRoleDef rd join (select 'Database'5 as Scope, name as RoleName from sys.database_principals where type = 'R' union all select 'Server', name from sys.server_principals where type = 'R' )6 r on r.Scope = rd.Scope and r.Rolename = case r.Scope when 'Database' then rd.RoleName Collate SQL_Latin1_General_CP1_CS_AS  else left(rd.Rolename + '_'7 + @DatabaseName, 128) Collate SQL_Latin1_General_CP1_CS_AS  end where rd.UID_QBMDBRoleDef = @UID_QBMDBRoleDef if isnull(@RoleName, '') = ''  begin goto8 EndLabel end if @Scope = 'Database' begin delete  @ElementBuffer  insert into @ElementBuffer(Ident1, ContentFull) select 'revoke', 'Revoke ' + p.permission_name9 Collate SQL_Latin1_General_CP1_CS_AS  + ' ON DATABASE::' + @DatabaseName + ' FROM ' + r.RoleName from sys.database_principals rp join QBMDBRoleDef r on10 r.RoleName = rp.name join sys.database_permissions p on rp.principal_id = p.grantee_principal_id where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and rp.type11 = 'R' and p.state = 'G' and p.class_desc = 'DATABASE' and not exists (select top 1 1 from QBMDBRightsAddOn a where a.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef12 and a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS  and a.ObjectClass = 'DATABASE' ) union all select 'revoke', 'Revoke ' +13 p.permission_name Collate SQL_Latin1_General_CP1_CS_AS  + ' ON OBJECT::' + o.name + ' FROM ' + r.RoleName from sys.database_principals rp join QBMDBRoleDef14 r on r.RoleName = rp.name join sys.database_permissions p on rp.principal_id = p.grantee_principal_id join sys.objects o on p.major_id = o.object_id where15 r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and rp.type = 'R' and p.state = 'G'  and o.type <> 'S' and not exists (select top 1 1 from QBMDBRightsAddOn a where16 a.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef and a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS  and a.ObjectName = o.name ) union17 all select 'revoke', 'Revoke ' + p.permission_name Collate SQL_Latin1_General_CP1_CS_AS  + ' ON TYPE::' + o.name + ' FROM ' + r.RoleName from sys.database_principals18 rp join QBMDBRoleDef r on r.RoleName = rp.name join sys.database_permissions p on rp.principal_id = p.grantee_principal_id join sys.table_types o on p.major_id19 = o.user_type_id where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and rp.type = 'R' and p.state = 'G' and p.class_desc = 'TYPE' and not exists (select top20 1 1 from QBMDBRightsAddOn a where a.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef and a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS21  and a.ObjectName = o.name and a.ObjectClass = 'TYPE' ) union all select 'revoke', 'Revoke ' + p.permission_name Collate SQL_Latin1_General_CP1_CS_AS 22 + ' ON SCHEMA::"' + o.name + '" FROM ' + r.RoleName from sys.database_principals rp join QBMDBRoleDef r on r.RoleName = rp.name join sys.database_permissions23 p on rp.principal_id = p.grantee_principal_id join sys.schemas o on p.major_id = o.schema_id where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and rp.type 24= 'R' and p.state = 'G' and p.class_desc = 'SCHEMA' and not exists (select top 1 1 from QBMDBRightsAddOn a where a.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef25 and a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS  and a.ObjectName = o.name and a.ObjectClass = 'SCHEMA' ) union all select26 'revoke', 'Revoke ' + p.permission_name Collate SQL_Latin1_General_CP1_CS_AS  + ' ON USER::' + o.name + ' FROM ' + r.RoleName from sys.database_principals27 rp join QBMDBRoleDef r on r.RoleName = rp.name join sys.database_permissions p on rp.principal_id = p.grantee_principal_id join sys.database_principals28 o on p.major_id = o.principal_id where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and rp.type = 'R' and p.state = 'G' and p.class_desc = 'DATABASE_PRINCIPAL'29 and not exists (select top 1 1 from QBMDBRightsAddOn a where a.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef and a.PermissionName = p.permission_name Collate 30SQL_Latin1_General_CP1_CS_AS  and a.ObjectName = o.name and a.ObjectClass = 'USER' ) union all select 'grant', 'Grant ' + a.PermissionName + ' ON DATABASE::'31 + @DatabaseName + ' TO ' + r.RoleName from QBMDBRightsAddOn a join QBMDBRoleDef r on r.UID_QBMDBRoleDef = a.UID_QBMDBRoleDef where r.UID_QBMDBRoleDef 32= @UID_QBMDBRoleDef and a.ObjectClass = 'DATABASE' and not exists (select top 1 1 from sys.database_principals rp join sys.database_permissions p on rp.principal_id33 = p.grantee_principal_id where rp.type = 'R' and p.state = 'G' and a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS  and p.class_desc34 = a.ObjectClass Collate SQL_Latin1_General_CP1_CS_AS  and rp.name = r.Rolename ) union all select 'grant', 'Grant ' + a.PermissionName + ' ON OBJECT::'35 + a.ObjectName + ' TO ' + r.RoleName from QBMDBRightsAddOn a join QBMDBRoleDef r on r.UID_QBMDBRoleDef = a.UID_QBMDBRoleDef join sys.objects o on a.ObjectName36 = o.name where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and not exists (select top 1 1 from sys.database_principals rp join sys.database_permissions p on37 rp.principal_id = p.grantee_principal_id where rp.type = 'R' and p.state = 'G' and o.type <> 'S' and a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS38  and rp.name = r.Rolename and p.major_id = o.object_id ) union all select 'grant', 'Grant ' + a.PermissionName + ' ON TYPE::' + a.ObjectName + ' TO ' 39+ r.RoleName from QBMDBRightsAddOn a join QBMDBRoleDef r on r.UID_QBMDBRoleDef = a.UID_QBMDBRoleDef join sys.table_types o on a.ObjectName = o.name where40 r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and a.ObjectClass = 'TYPE' and not exists (select top 1 1 from sys.database_principals rp join sys.database_permissions41 p on rp.principal_id = p.grantee_principal_id where rp.type = 'R' and p.state = 'G' and a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS42  and rp.name = r.Rolename and p.major_id = o.user_type_id ) union all select 'grant', 'Grant ' + a.PermissionName + ' ON SCHEMA::"' + a.ObjectName + '" TO '43 + r.RoleName from QBMDBRightsAddOn a join QBMDBRoleDef r on r.UID_QBMDBRoleDef = a.UID_QBMDBRoleDef join sys.schemas o on a.ObjectName = o.name where 44r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and a.ObjectClass = 'SCHEMA' and not exists (select top 1 1 from sys.database_principals rp join sys.database_permissions45 p on rp.principal_id = p.grantee_principal_id where rp.type = 'R' and p.state = 'G' and a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS46  and rp.name = r.Rolename and p.major_id = o.schema_id ) union all select 'grant', 'Grant ' + a.PermissionName + ' ON USER::' + a.ObjectName + ' TO ' 47+ r.RoleName from QBMDBRightsAddOn a join QBMDBRoleDef r on r.UID_QBMDBRoleDef = a.UID_QBMDBRoleDef join sys.database_principals o on a.ObjectName = o.name48 where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and a.ObjectClass = 'USER' and not exists (select top 1 1 from sys.database_principals rp join sys.database_permissions49 p on rp.principal_id = p.grantee_principal_id where rp.type = 'R' and p.state = 'G' and a.PermissionName = p.permission_name Collate SQL_Latin1_General_CP1_CS_AS50  and rp.name = r.Rolename and p.major_id = o.principal_id ) select @ElementCount = count(*) from @ElementBuffer select @ElementIndex = 1 while @ElementIndex51 <= @ElementCount begin select top 1 @SQLcmd = bu.ContentFull from @ElementBuffer bu where bu.ElementIndex = @ElementIndex exec sp_executesql @SQLCmd select52 @ElementIndex += 1 end  end  if @Scope = 'Server' and dbo.QBM_FGIServerEngineProperties('AccessToServerPrincipal') = 1  begin delete  @ElementBuffer  53insert into @ElementBuffer(Ident1, ContentFull) select 'revoke', 'Revoke ' + p.permission_name Collate SQL_Latin1_General_CP1_CS_AS  + ' FROM ' + left(r.RoleName54 + '_' + @DatabaseName, 128) from sys.server_principals rp join QBMDBRoleDef r on left(r.RoleName + '_' + @DatabaseName, 128) = rp.name Collate SQL_Latin1_General_CP1_CS_AS55  join sys.server_permissions p on rp.principal_id = p.grantee_principal_id where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and rp.type = 'R' and p.state 56= 'G' and not exists (select top 1 1 from QBMDBRightsAddOn a where a.UID_QBMDBRoleDef = r.UID_QBMDBRoleDef and a.PermissionName = p.permission_name Collate57 SQL_Latin1_General_CP1_CS_AS  and p.class_desc = a.ObjectClass Collate SQL_Latin1_General_CP1_CS_AS  and a.ObjectClass = 'SERVER' ) union select 'grant'58, 'Grant ' + a.PermissionName + ' TO ' + left(r.RoleName + '_' + @DatabaseName, 128) from QBMDBRightsAddOn a join QBMDBRoleDef r on r.UID_QBMDBRoleDef 59= a.UID_QBMDBRoleDef where r.UID_QBMDBRoleDef = @UID_QBMDBRoleDef and a.ObjectClass = 'SERVER' and not exists (select top 1 1 from sys.server_principals60 rp join sys.server_permissions p on rp.principal_id = p.grantee_principal_id where rp.type = 'R' and p.state = 'G' and a.PermissionName = p.permission_name61 Collate SQL_Latin1_General_CP1_CS_AS  and p.class_desc = a.ObjectClass Collate SQL_Latin1_General_CP1_CS_AS  and left(r.RoleName + '_' + @DatabaseName62, 128) = rp.name Collate SQL_Latin1_General_CP1_CS_AS  ) select @ElementCount = count(*) from @ElementBuffer select @ElementIndex = 1 while @ElementIndex63 <= @ElementCount begin select top 1 @SQLcmd = bu.ContentFull from @ElementBuffer bu where bu.ElementIndex = @ElementIndex exec @master @SQLCmd select 64@ElementIndex += 1 end  end  EndLabel: return END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 65