Snippet Name: boca5

Tags: helpful,scriptgeneration

Created Date: Oct 1 2021 8:05AM
Last Modified Date: Apr 1 2022 11:23AM

–desc: boca helper function

Dependencies: none-standalone code

default description

Sample Output:

 
 --desc: boca helper function
 
 {C}
 
 DECLARE @OffendingWhereStatement VARCHAR(MAX) = '[Enrollment].[EnrollmentIndex] IN ( Select enrollmentIndex from enrollment (nolock) inner join section (nolock) on section.sectionindex = enrollment.s'
 
 SELECT @OffendingWhereStatement = REPLACE(REPLACE(REPLACE(REPLACE(@OffendingWhereStatement,CHAR(13),''),CHAR(10),''),CHAR(9),''),CHAR(32),'')
 
 ;WITH T1
 
 AS
 
 (
 
 SELECT DISTINCT 
 
 @@SERVERNAME AS ServerName,
 
 DB_NAME() AS DataBaseName,
 
 tabz.[TableMapIndex],
 
 tabz.[TableName],
 
 Role.Code,
 
 REPLACE(REPLACE(REPLACE(REPLACE(sec.[SecuritySql],CHAR(13),''),CHAR(10),''),CHAR(9),''),CHAR(32),'') AS StrippedSecuritySql,
 
 HASHBYTES('SHA1',REPLACE(REPLACE(REPLACE(REPLACE(sec.[SecuritySql],CHAR(13),''),CHAR(10),''),CHAR(9),''),CHAR(32),'')) AS StrippedSecurityHash,
 
 HASHBYTES('SHA1',tabz.[SQLSelect]) AS SQLSelectHash,
 
 HASHBYTES('SHA1',sec.[SecuritySql]) AS SecuritySqlHash,
 
 Role.RoleIndex,
 
 tabz.[SQLSelect],
 
 --tabz.[SQLAdd],
 
 --tabz.[SQLUpdate],
 
 --tabz.[SQLDelete],
 
 --tabz.[IsAudited],
 
 sec.[SecuritySql],
 
 SecurityRuleIndex,
 
 SelUpdateSQL = 'UPDATE tabz SET SQLSelect  = '''  + REPLACE(tabz.SQLSelect,'''','''''') + ''' FROM [BocaVoxSystem].[TableMap] WHERE TableName = ''' + tabz.TableName + '''',
 
 SelRollbackSQL = 'UPDATE tabz SET SQLSelect  = '''  + REPLACE(tabz.SQLSelect,'''','''''') + ''' FROM [BocaVoxSystem].[TableMap] WHERE TableName = ''' + tabz.TableName + '''',
 
 SecUpdateSQL = 'UPDATE sec SET SecuritySql = '''  + REPLACE(sec.[SecuritySql],'''','''''') + '''
 
  FROM [BocaVoxSystem].[TableMap] tabz
 
    INNER JOIN BocaVoxSystem.SecurityRule sec
 
      ON tabz.[TableMapIndex] = sec.[TableMapIndex] --AND tabz.RoleIndex = sec.RoleIndex
 
  INNER JOIN BocaVoxSystem.Role 
 
    ON Role.RoleIndex = sec.RoleIndex
 
  WHERE  Role.Code = ''' + Role.Code +''' AND tabz.TableName = ''' + tabz.TableName + ''';',
 
 SecRollbackSQL = 'UPDATE sec SET SecuritySql = '''  + REPLACE(sec.[SecuritySql],'''','''''') + '''
 
  FROM [BocaVoxSystem].[TableMap] tabz
 
    INNER JOIN BocaVoxSystem.SecurityRule sec
 
      ON tabz.[TableMapIndex] = sec.[TableMapIndex] --AND tabz.RoleIndex = sec.RoleIndex
 
  INNER JOIN BocaVoxSystem.Role 
 
      ON Role.RoleIndex = sec.RoleIndex
 
  WHERE  Role.Code = ''' + Role.Code +''' AND tabz.TableName = ''' + tabz.TableName + ''';'
 
  FROM [BocaVoxSystem].[TableMap] tabz
 
  INNER JOIN BocaVoxSystem.SecurityRule sec
 
  ON tabz.[TableMapIndex] = sec.[TableMapIndex] --AND tabz.RoleIndex = sec.RoleIndex
 
  INNER JOIN BocaVoxSystem.Role ON Role.RoleIndex = sec.RoleIndex
 
 
 
 
 
 )
 
 SELECT * FROM T1
 
 WHERE 1 = 1
 
 --AND TableName = 'Enrollment'
 
 AND CHARINDEX(LEFT(@OffendingWhereStatement,30),StrippedSecuritySql) > 0
 
 
 
 
 
 ORDER BY TableName,SecuritySqlHash