none
SQLServer 2008 R2: The SCHEMA LOCK permission was denied on the object

    Question

  • Hi all,

    I encounter the following error while developing a SSRS project that connects to a SQL Server Database view:

    "Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
    The SCHEMA LOCK permission was denied on the object 'Table4', database 'DBRemote', schema 'dbo'."

    That view uses a linked server to select data from a remote SQL Server Database (SQL Server 2005).

    There are no sql hints specified in my views

    My view T-SQL is:

    Select

    ...

    From linksv.DBRemote.dbo.Table1 T1

    Inner Join linksv.DBRemote.dbo.Table2 T2 On

      T1.fk1 = T2.pk1

    Inner Join view1 v1 On

      T2.fk2 = v1.pk2

     

    My t-sql for view1 is:

    Select

    ...

    From linksv.DBRemote.dbo.Table3 T3 

    Inner Join linksv.DBRemote.dbo.Table4 T4 On

      t3.fk1 = T4.pk1

     

    The object specified in error message above refers to Table "linksv.DBRemote.dbo.Table4" (see view above)

     

    SQL Server Permissions are set for all objects involved in the queries above.

     

    The funny thing is that the error occurs when I run my report from the report server webinterface

    and my report project is loaded in BIDS at the same time.

    The error occurs when I execute the query in SSMS 2008 and also when I run the query

    in BIDS 2008 Query designer.

     

    I also wondering why the error referes to the "linksv.DBRemote.dbo.Table4" remote object only

    but not to the other remote objects in that query.

     

    Im not sure where to look any further on what might cause this error.

    Appreciate any help very much.

    Thanks

    Bodo





    • Edited by bvuHH Wednesday, December 28, 2011 3:18 PM
    Wednesday, December 28, 2011 2:01 PM

Answers

  • It certainly sounds like a permission error. But the strange thing is that I don't know of any SCHEMA LOCK permission, and it is not documented in Books Online. I have an appointment with my pillow in just a few minutes, so I don't have any time for further research right now.

    I would recommend that you investigate your permissions on the remote setver. Also, searching for "SCHEMA LOCK permission was denied" on Google may give some leads.

    As for why you get it only on Table4 - try taking out of the query. It may be Table3 next time. Or not.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Tuesday, January 03, 2012 7:51 AM
    Wednesday, December 28, 2011 11:20 PM

All replies

  • Can you please post the error message?
    http://uk.linkedin.com/in/ramjaddu
    Wednesday, December 28, 2011 2:44 PM
  • "Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
    The SCHEMA LOCK permission was denied on the object 'Table4', database 'DBRemote', schema 'dbo'."

    Thanks

    Bodo

    Wednesday, December 28, 2011 3:19 PM
  • It certainly sounds like a permission error. But the strange thing is that I don't know of any SCHEMA LOCK permission, and it is not documented in Books Online. I have an appointment with my pillow in just a few minutes, so I don't have any time for further research right now.

    I would recommend that you investigate your permissions on the remote setver. Also, searching for "SCHEMA LOCK permission was denied" on Google may give some leads.

    As for why you get it only on Table4 - try taking out of the query. It may be Table3 next time. Or not.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Tuesday, January 03, 2012 7:51 AM
    Wednesday, December 28, 2011 11:20 PM
  • Does the error still occur if you remove the Alias name for your linked server i.e. Use linksv.DBRemote.dbo.Table4 instead of T4


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Wednesday, December 28, 2011 11:51 PM
  • yes, this error happens because the login that is mapped on the second side of the linked-server is missing the read permission on the object. All queries done trhough linked-server will acquire a schema-lock just so that SQL can read the results correctly. I don't know exactly WHY it does it this way, but it does.

    to fix the error message, give the required permission to the login on the server that is target of the linked-server configuration - be it Windows Authentication, SQL Login, or connections "made using the logins current security context". The preferable way is to map 1-to-1 every login that will be used in the Security tab of the Linked Server Properties page.

    I made a post about this: http://thelonelydba.wordpress.com/2013/04/17/sql-and-linked-servers-the-schema-lock-permission-was-denied-on-the-object/

    Friday, April 19, 2013 6:34 PM
  • I get this error periodically despite being logged in as dbo. However, the database on the linked server is created by a third party app that seems to generate 1000s of tables. Sometimes this error occurs and sometimes it does not. When it does, refreshing the linked tables and running a select query on one of the tables in the linked server seems to clear the error. I'm not quite sure why though - maybe it is something to do with the list of tables on the linked server not getting updated correctly?

    Monday, July 28, 2014 2:32 PM