question

Hi,
We are seeing a high volume of deadlocks since we introduced Vault process that gives permissions on schema.
Here the process1 runs the following GRANT statements
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA :: sharedproxy TO [v-tk-ca7baef9-f8e0---xHwo6QQTzfhZKx7y7zqM-1638452722]
Process 1 holds a SCH_M lock on the resource 'SECURITY CACHE' under the transaction name 'SEC Cache Coherency'

process 2 runs a stored proc mar_dev.dbo.spDD_MarriageView_Search which has dynamic sql that uses sp_executesql to insert into a #temp table.
This has never been a problem before. Now from the deadlock graph we can see that sp_executesql holds a SCH_M lock on METADATA: database_id = 39 PERMISSIONS(class = 0, major_id = 0) under the transactionname 'read permissions'
This is interfering with SECURITY CACHE from process 1.

There is no documentation around xml nodes transaction name any where.
Could someone please throw some light on why sp_executesql is accessing PERMISSIONS for a simple insert into #temp table.
Any ideas to resolve this deadlocks?

  
                

                 
 <deadlock>
                 
   <victim-list>
                 
     <victimProcess id="process2c98d755468" />
                 
   </victim-list>
                 
   <process-list>
                 
     <process id="process2c98d755468" taskpriority="0" logused="0" waitresource="METADATA: database_id = 39 SECURITY_CACHE($hash = 0x27:0x0), lockPartitionId = 0" waittime="4309" ownerId="17175321444" transactionname="SEC Cache Coherency" lasttranstarted="2021-12-02T08:45:23.043" XDES="0x2cea0dd1a40" lockMode="Sch-M" schedulerid="1" kpid="10016" status="suspended" spid="101" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-12-02T08:45:23.040" lastbatchcompleted="2021-12-02T08:45:23.040" lastattention="1900-01-01T00:00:00.040" clientapp="mar-vault-appdb" hostname="myhost-65" hostpid="0" loginname="sp-vault-hash-d" isolationlevel="read committed (2)" xactid="17175320772" currentdb="39" currentdbname="mar_dev" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
                 
       <executionStack>
                 
         <frame procname="adhoc" line="1" sqlhandle="0x01002700b2bb6835407e560ed102000000000000000000000000000000000000000000000000000000000000">
                 
 GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE  ON SCHEMA :: sharedproxy   TO [v-tk-ca7baef9-f8e0---xHwo6QQTzfhZKx7y7zqM-1638452722]    </frame>
                 
       </executionStack>
                 
       <inputbuf>
                 
 GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE  ON SCHEMA :: sharedproxy   TO [v-tk-ca7baef9-f8e0---xHwo6QQTzfhZKx7y7zqM-1638452722]   </inputbuf>
                 
     </process>
                 
     <process id="process2c98d755088" taskpriority="0" logused="0" waitresource="METADATA: database_id = 39 PERMISSIONS(class = 0, major_id = 0), lockPartitionId = 0" waittime="4309" ownerId="17175321432" transactionname="read permissions" lasttranstarted="2021-12-02T08:45:23.043" XDES="0x2ca8a173a40" lockMode="Sch-S" schedulerid="1" kpid="832" status="suspended" spid="450" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2021-12-02T08:45:22.987" lastbatchcompleted="2021-12-02T08:45:22.987" lastattention="1900-01-01T00:00:00.987" clientapp="DataSvc" hostname="ase-rate-59" hostpid="5404" loginname="dm\sp-rate-d" isolationlevel="read committed (2)" xactid="17175321385" currentdb="39" currentdbname="mar_dev" lockTimeout="4294967295" clientoption1="536870944" clientoption2="128056">
                 
       <executionStack>
                 
         <frame procname="adhoc" line="2" stmtstart="12" stmtend="562" sqlhandle="0x020000004f75a605535205b81f671cce718aff7a9a3bd5fb0000000000000000000000000000000000000000">
                 
 unknown    </frame>
                 
         <frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
                 
 sp_executesql    </frame>
                 
         <frame procname="mar_dev.dbo.spDD_MarriageView_Search" line="59" stmtstart="3458" stmtend="3502" sqlhandle="0x030027004f2a4e475121f000bca4000001000000000000000000000000000000000000000000000000000000">
                 
 EXEC sp_executesql @sq    </frame>
                 
       </executionStack>
                 
       <inputbuf>
                 
 Proc [Database Id = 39 Object Id = 1196304975]   </inputbuf>
                 
     </process>
                 
   </process-list>
                 
   <resource-list>
                 
     <metadatalock subresource="SECURITY_CACHE" classid="$hash = 0x27:0x0" dbid="39" lockPartition="0" id="lock2d0eaa62f80" mode="Sch-M">
                 
       <owner-list>
                 
         <owner id="process2c98d755088" mode="Sch-M" />
                 
       </owner-list>
                 
       <waiter-list>
                 
         <waiter id="process2c98d755468" mode="Sch-M" requestType="wait" />
                 
       </waiter-list>
                 
     </metadatalock>
                 
     <metadatalock subresource="PERMISSIONS" classid="class = 0, major_id = 0" dbid="39" lockPartition="0" id="lock2d050969700" mode="Sch-M">
                 
       <owner-list>
                 
         <owner id="process2c98d755468" mode="Sch-M" />
                 
       </owner-list>
                 
       <waiter-list>
                 
         <waiter id="process2c98d755088" mode="Sch-S" requestType="wait" />
                 
       </waiter-list>
                 
     </metadatalock>
                 
   </resource-list>
                 
 </deadlock>