While debugging some legacy applications, I’ve come to this error. The exact message is:
Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1 The SCHEMA LOCK permission was denied on the object 'X', database 'Y', schema 'dbo'.
I suspected some permission related issue (that is very common with linked-servers). A quick search later confirmed it, but as documentation is pretty rare on this issue I decided to post this information to help someone in the future.
Usually the best practice for linked-servers is to map every login used on both sides to make sure all is secure. But on real world of already deployed systems the reality is that things are (almost) never how it should be.
The fact that caused this strange message is the option to map the remote logins “using the login’s current security context” on the linked-server configuration page. If the same login used on the first side of the link does exist on the second but doesn’t have access to the correct object, this strange schema error will appear. To fix it simply give the required permission on the object at the second side of the linked-server.
I know, I know, this message should not appear on a simple select, for example. But it does. And this fixes it. Life moves on.