SQL and Linked-Servers: The SCHEMA LOCK permission was denied on the object

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.

cya!

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.

View all posts by mauriciorpp →

2 Responses to SQL and Linked-Servers: The SCHEMA LOCK permission was denied on the object

Tony Henrich says:

October 19, 2020 at 16:45

“To fix it simply give the required permission on the object at the second side of the linked-server”

Which required [ermission?? I tried grant ‘GRANT ‘SCHEMA lock’ on.. and other variations and they didn’t work. It would have been more helpful if you posted an example.

- mauriciorpp says:
  
  October 19, 2020 at 23:54
  
  Hi Tony, I apologize this is an old post and I lost the exact command to the sands of time… but after re-reading the other links it seems the fix was to give read permissions on the table at the target server, for the exact same login you are using in your linked-server configuration. Or try adding permission to run sp_getschemalock for it too. I can not replicate this at the moment, but if I do find some time to build a new VM lab setup I could revisit this topic.
  
  Anyway, I would advise you to move away from Linked-Servers and try to use more modern technologies, such as SSIS, if at all possible.
  
  Thanks for your comment!