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.



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.
This entry was posted in SQLServer and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s