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 :: shared_proxy 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 hardly any documentation around internal of sql server (security_cache).
Vault seems to be blocking pretty much every sql not just dynamic sql.
Although deadlocks are high with dynamic sql.
Any ideas on how to effectively implement vault in sql server?