Error 229 SCHEMA LOCK permission on linked server RRS feed

  • Question

  • I am using SSRS with stored windows credentials for a user that is part of a group which has access to both my SQL Server and its linked server. I was getting the 229 error stating that there was a SCHEMA LOCK permission denying access. I did some research on the problem and found a suggestion saying I should try turning on cross database ownership chaining on my server. I did this and now I'm receiving the following error instead:

    TITLE: Microsoft SQL Server Report Builder

    An error occurred while executing the query.
    The OLE DB provider "SQLNCLI11" for linked server "SQLSERVER\INSTANCENAME" does not contain the table ""DataBaseName"."dbo"."table_name"". The table either does not exist or the current user does not have permissions on that table. (Microsoft SQL Server, Error: 7314)

    Now, if I shut off cross database ownership chaining on my server, I still get the 7314 error instead of the schema permission error.

    Thursday, February 2, 2017 4:49 PM

All replies

  • Hi ,

    When querying the linked server table you need to specify the fully qualified name., for example: [ServerName].[DATABASEName].[dbo].[link_test]. Additionally, please check whether we can see the table with SQL Server Management Studio under Linked Servers. If so, please right click the table, “Script table as”,  “Select To”, “New Query Window” to generate the query codes.

    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Thursday, February 2, 2017 4:59 PM
  • Vishe,

    Thanks for the help. However, I'm already using the FQDN to query the linked server.

    Thursday, February 2, 2017 5:53 PM
  • Hi Technobeam,

    How do you map logins when creating linked server? Are you able to execute some linked queries in SQL Server Management Studio using the user login?

    Regarding to the original error 229, just give the required permission on the object at the second side of  the linked server for the mapped login, for more details, please review this similar blog: https://thelonelydba.wordpress.com/2013/04/17/sql-and-linked-servers-the-schema-lock-permission-was-denied-on-the-object/ .

    Lydia Zhang

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 3, 2017 7:23 AM