Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1042870%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23114%3A%20Could%20Row%20Level%20Security%20changes%20the%20execution%20plan%20of%20a%20query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1042870%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20that%20remember%20me%20the%20impact%20that%20we%20could%20have%20using%20row%20level%20security%20afterwards%20that%20our%20databases%20has%20been%20developed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20customer%20reported%20a%20performance%20issue%20after%20implementing%20the%20row%20level%20security.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-1056681982%22%20id%3D%22toc-hId-1056681982%22%20id%3D%22toc-hId-1056681982%22%3ECurrent%20situation%3C%2FH3%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOur%20customer%20have%20the%20following%20design%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3E%26nbsp%3BA%20table%20where%20we%20have%20the%20LogData%2C%20that%20contains%20data%20and%20group%20code.%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CDIV%20style%3D%22padding-left%3A%2060px%3B%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3ECREATE%20TABLE%20%5Bdbo%5D.%5BLogData%5D(%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BInsertID%5D%20%5Bbigint%5D%20NOT%20NULL%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BID%5D%20%5Bvarchar%5D(100)%20NOT%20NULL%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bgroupcode%5D%20%5Bvarchar%5D(255)%20NOT%20NULL%2C%3CBR%20%2F%3E%26nbsp%3BCONSTRAINT%20%5BPK_LogData_current%5D%20PRIMARY%20KEY%20CLUSTERED%20%3CBR%20%2F%3E%26nbsp%3B(%5BID%5D%20ASC)%20WITH%20(STATISTICS_NORECOMPUTE%20%3D%20OFF%2C%20IGNORE_DUP_KEY%20%3D%20OFF)%20ON%20%5BPRIMARY%5D%3CBR%20%2F%3E)%20ON%20%5BPRIMARY%5D%3CBR%20%2F%3EGO%3C%2FFONT%3E%3C%2FDIV%3E%0A%3CDIV%20style%3D%22padding-left%3A%2060px%3B%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EALTER%20TABLE%20dbo.%5BLogData%5D%20ADD%26nbsp%3B%20DEFAULT%20('G1')%20FOR%20%5Bgroupcode%5D%3CBR%20%2F%3EGO%3C%2FFONT%3E%3C%2FDIV%3E%0A%3CDIV%20style%3D%22padding-left%3A%2060px%3B%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3Ecreate%20index%20LogData1%20on%20%5Bdbo%5D.%5BLogData%5D%20(%5BInsertiD%5D)%3C%2FFONT%3E%3C%2FDIV%3E%0A%3CDIV%20style%3D%22padding-left%3A%2060px%3B%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EI%20have%20a%20table%20relation%20with%20group_user%20that%20contains%20per%20user%20that%20groups%20that%20belongs%20to%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20style%3D%22padding-left%3A%2060px%3B%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3Ecreate%20table%20dbo.group_user%20(%5Bgroupcode%5D%20varchar(255)%2Cusername%20varchar(128))%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2060px%3B%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EINSERT%20INTO%26nbsp%3B%20dbo.group_user%20VALUES('G1'%2C1)%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20inherit%3B%20font-family%3A%20inherit%3B%20font-size%3A%2018px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20normal%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.2%3B%20margin-bottom%3A%2012px%3B%20margin-top%3A%2024px%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%20id%3D%22toc-hId--750772481%22%20id%3D%22toc-hId--750772481%22%20id%3D%22toc-hId--750772481%22%3EInsert%20some%20rows%20in%20the%20table%20LogData%20to%20have%20some%20data%20to%20play%3C%2FH3%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EDECLARE%20%40n%20as%20integer%20%3D%200%3CBR%20%2F%3EWHILE(%40n%26lt%3B%3D100000)%3CBR%20%2F%3Ebegin%3CBR%20%2F%3E%26nbsp%3BSET%20%40n%3D%40n%2B1%3CBR%20%2F%3E%26nbsp%3Binsert%20into%20LogData%20(%5BInsertID%5D%2C%20%5BID%5D)%20values(%40N%2C%40N)%3CBR%20%2F%3Eend%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20inherit%3B%20font-family%3A%20inherit%3B%20font-size%3A%2018px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20normal%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.2%3B%20margin-bottom%3A%2012px%3B%20margin-top%3A%2024px%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%20id%3D%22toc-hId-1736740352%22%20id%3D%22toc-hId-1736740352%22%20id%3D%22toc-hId-1736740352%22%3EQuery%20Execution%20before%20adding%20row%20level%20security%3C%2FH3%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EWhen%20I%20run%20the%20query%3A%20%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3ESELECT%20MAX(INSERTID)%20FROM%20LogData%20the%20execution%20plan%20looks%20like%20normal%20based%20on%20the%20index%20that%20I%20have%3A%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2060px%3B%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20560px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159650i0FEF2B2BD6477618%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Issu1.PNG%22%20title%3D%22Issu1.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20inherit%3B%20font-family%3A%20inherit%3B%20font-size%3A%2018px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20normal%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.2%3B%20margin-bottom%3A%2012px%3B%20margin-top%3A%2024px%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%20id%3D%22toc-hId--70714111%22%20id%3D%22toc-hId--70714111%22%20id%3D%22toc-hId--70714111%22%3ERow%20Level%20Security%20Implementation%3C%2FH3%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EI%20created%20the%20function%3A%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20style%3D%22padding-left%3A%2060px%3B%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3ECREATE%20FUNCTION%20dbo.%5Bfn_securitypredicate%5D(%40groupcode%20AS%20varchar(255))%20%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20RETURNS%20TABLE%20%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WITH%20SCHEMABINDING%20%3CBR%20%2F%3EAS%20%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20RETURN%20SELECT%201%20AS%20fn_securitypredicate_result%3CBR%20%2F%3EWHERE%20%40groupcode%20IN%20(%3CBR%20%2F%3ESELECT%20dbo.group_user.%5Bgroupcode%5D%3CBR%20%2F%3E%26nbsp%3BFROM%20dbo.group_user%3CBR%20%2F%3EWHERE%26nbsp%3B%20dbo.group_user.username%20%3D%3CBR%20%2F%3ECASE%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%20is_member('G1')%20%3D%201%20THEN%20CAST('G1'%20AS%20nvarchar(128))%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%20is_member('G2')%20%3D%201%20THEN%20CAST('G2'%20As%20nvarchar(128))%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%20is_member('G3')%20%3D%201%20THEN%20CAST('G3'%20As%20nvarchar(128))%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ELSE%20user_name()%3CBR%20%2F%3EEND)%3B%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2060px%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%20style%3D%22box-sizing%3A%20border-box%3B%20clear%3A%20left%3B%20color%3A%20%23333333%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20list-style-image%3A%20none%3B%20list-style-position%3A%20outside%3B%20list-style-type%3A%20disc%3B%20margin-bottom%3A%2012px%3B%20margin-top%3A%200px%3B%20orphans%3A%202%3B%20padding-left%3A%202.5em%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E%0A%3CLI%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%22%3E%3CSTRONG%3EI%20created%20the%20security%20policy%3A%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20style%3D%22padding-left%3A%2060px%3B%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3ECREATE%20SECURITY%20POLICY%20dbo.%5BLogData_Check%5D%20%3CBR%20%2F%3EADD%20FILTER%20PREDICATE%20dbo.%5Bfn_securitypredicate%5D(%5Bgroupcode%5D)%20ON%20dbo.Logdata%3CBR%20%2F%3EWITH%20(STATE%20%3D%20ON%2C%20SCHEMABINDING%20%3D%20ON)%3CBR%20%2F%3EGO%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2060px%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EWhen%20I%20executed%20the%20query%20SELECT%20MAX(INSERTID)%20FROM%20LogData%20%2C%20I%20found%20a%20different%20execution%20plan.%3C%2FFONT%3E%3C%2FSTRONG%3E%3CUL%3E%0A%3CLI%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EI%20found%20that%20SQL%20Server%20will%20execute%20a%20Clustered%20Index%20Scan%20instead%20of%20Index%20Scan.%20Why%3F%20because%20it%20needs%20to%20access%20the%20groupcode%20to%20check%20the%20security%20predicate.%3C%2FFONT%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2060px%3B%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20775px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159651iBA8576B918A2BEC9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Issue2.PNG%22%20title%3D%22Issue2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2060px%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20inherit%3B%20font-family%3A%20inherit%3B%20font-size%3A%2018px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20normal%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.2%3B%20margin-bottom%3A%2012px%3B%20margin-top%3A%2024px%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%20id%3D%22toc-hId--1878168574%22%20id%3D%22toc-hId--1878168574%22%20id%3D%22toc-hId--1878168574%22%3EWorkaround%20to%20have%20the%20same%20execution%20plan%3C%2FH3%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EModify%20or%20create%20an%20index%20that%20contains%20the%20groupcode%20on%20it%2C%20either%20adding%20as%20a%20sorted%20column%20or%20included%20column.%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20style%3D%22padding-left%3A%2060px%3B%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3Ecreate%20index%20LogData2%20on%20%5Bdbo%5D.LogData%20(%5BInsertiD%5D%2C%5Bgroupcode%5D)%3CBR%20%2F%3Ecreate%20index%20LogData3%20on%20%5Bdbo%5D.LogData%20(%5BInsertiD%5D)%20include%20(%5Bgroupcode%5D)%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2060px%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%22%3E%3CSTRONG%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%20box-sizing%3A%20border-box%3B%22%3EWhen%20I%20executed%20the%20query%20SELECT%20MAX(INSERTID)%20FROM%20LogData%20%2C%20I%20found%20a%20very%20similar%26nbsp%3B%20execution%20plan%20to%20the%20first%20one%20using%20an%20index%20scan%2C%20because%20the%20index%20contains%20the%20field%20to%20check%20the%20security%20function.%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%20box-sizing%3A%20border-box%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20743px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159652iA303C9F87408295D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Issu3.PNG%22%20title%3D%22Issu3.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EEnjoy!%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1042870%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20that%20remember%20me%20the%20impact%20that%20we%20could%20have%20using%20row%20level%20security%20afterwards%20that%20our%20databases%20has%20been%20developed.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1051434%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%23114%3A%20Could%20Row%20Level%20Security%20changes%20the%20execution%20plan%20of%20a%20query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1051434%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Jose%2C%20very%20helpful%20post.%3C%2FP%3E%3C%2FLINGO-BODY%3E

Today, I worked on a service request that remember me the impact that we could have using row level security afterwards that our databases has been developed.

 

A customer reported a performance issue after implementing the row level security.

 

Current situation

 

Our customer have the following design:

 

  •  A table where we have the LogData, that contains data and group code.
CREATE TABLE [dbo].[LogData](
    [InsertID] [bigint] NOT NULL,
    [ID] [varchar](100) NOT NULL,
    [groupcode] [varchar](255) NOT NULL,
 CONSTRAINT [PK_LogData_current] PRIMARY KEY CLUSTERED
 ([ID] ASC) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE dbo.[LogData] ADD  DEFAULT ('G1') FOR [groupcode]
GO
create index LogData1 on [dbo].[LogData] ([InsertiD])
 
  • I have a table relation with group_user that contains per user that groups that belongs to

create table dbo.group_user ([groupcode] varchar(255),username varchar(128))

INSERT INTO  dbo.group_user VALUES('G1',1)

 

Insert some rows in the table LogData to have some data to play

DECLARE @n as integer = 0
WHILE(@n<=100000)
begin
 SET @n=@n+1
 insert into LogData ([InsertID], [ID]) values(@N,@N)
end

 

Query Execution before adding row level security

  • When I run the query: SELECT MAX(INSERTID) FROM LogData the execution plan looks like normal based on the index that I have:

 

Issu1.PNG

 

Row Level Security Implementation

  • I created the function:

CREATE FUNCTION dbo.[fn_securitypredicate](@groupcode AS varchar(255))
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @groupcode IN (
SELECT dbo.group_user.[groupcode]
 FROM dbo.group_user
WHERE  dbo.group_user.username =
CASE
       WHEN is_member('G1') = 1 THEN CAST('G1' AS nvarchar(128))
       WHEN is_member('G2') = 1 THEN CAST('G2' As nvarchar(128))
       WHEN is_member('G3') = 1 THEN CAST('G3' As nvarchar(128))
       ELSE user_name()
END);

 

  • I created the security policy:

CREATE SECURITY POLICY dbo.[LogData_Check]
ADD FILTER PREDICATE dbo.[fn_securitypredicate]([groupcode]) ON dbo.Logdata
WITH (STATE = ON, SCHEMABINDING = ON)
GO

 

  • When I executed the query SELECT MAX(INSERTID) FROM LogData , I found a different execution plan.
    • I found that SQL Server will execute a Clustered Index Scan instead of Index Scan. Why? because it needs to access the groupcode to check the security predicate.

 

Issue2.PNG

 

Workaround to have the same execution plan

 

  • Modify or create an index that contains the groupcode on it, either adding as a sorted column or included column.

create index LogData2 on [dbo].LogData ([InsertiD],[groupcode])
create index LogData3 on [dbo].LogData ([InsertiD]) include ([groupcode])

 

  • When I executed the query SELECT MAX(INSERTID) FROM LogData , I found a very similar  execution plan to the first one using an index scan, because the index contains the field to check the security function.

 

Issu3.PNG

 

Enjoy!

 

 

 

 

1 Comment
Occasional Contributor

Thanks Jose, very helpful post.