schema lock permission denied

  • Hi all,

    I am using linked server to update some data on a target server.

    The linked server call fails with the error 'Schema lock permission denied on object xyz'.

    I have provided the neccassary rights(permission on the object xyz) for the login that calls the sp

    on the target server.

    I am using Windows Authentication..this problem occured when we moved fm Windows to SQL Authentication..

    SPN registraton, trusted domain ,delegation settings r done..

    Any clue wat can be the cause for this error..

    Thanks in Advance,

    Regards,

    S.V.Nagaraj

  • typo..

    Problem occured when we moved from SQL to Windows authentication

  • Hello,

    I would just like to clarify a few points:-

    1) When you say “delegation settings r done” does that mean you are using the Linked Server security option of “(connections will) Be made using the login’s current security context”?

    2) The SP is on the Linked Server, not the Local Server - correct?

    3) Are the Owners of the Object (XYZ) and the SP the same?

    4) What versions of SQL Server are the Linked and Local Servers?

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • Hi Marsh,

    Thanks for your efforts.

    My responses inline.

    I would just like to clarify a few points:-

    1) When you say “delegation settings r done” does that mean you are using the Linked Server security option of “(connections will) Be made using the login’s current security context”?

    --Yes.

    2) The SP is on the Linked Server, not the Local Server - correct?

    No. Sp is on the local server. A Few statements refer to a table in target server. Ex:

    Select * from trgserver.trgdbname.dbo.xyz,localdb.dbo.abc where ...

    Update trgserver.trgdbname.dbo.xyz set col1 =

    3) Are the Owners of the Object (XYZ) and the SP the same?

    --Yes

    4) What versions of SQL Server are the Linked and Local Servers?

    --SQL 2000 SP2 on both. Local is standard edition. Target is enterprise edition.

    Just noticed that the sp is using nolock on local server. Could that be a reason..

    ex:

    Select * from trgserver.trgdbname.dbo.xyz,localdb.dbo.abc (nolock) where ...

    Please give some clue..Thanks..

    Regards,

    S.V.Nagaraj

  • Hello again,

    Any chance you could test with the NoLock Hint commented out? At the least it would eliminate it from our inquiries 🙂

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • Hi Marsh,

    Err..Currently I cant check it..but will let you know if can get hold of the domain admin to

    do it or check it in anyother way..

    Even If you thro' a few possiblities on when I would get such a server it will be useful to me..

    Thanks..

  • Err typo..

    Even if u throw a few possiblities on when I get such a errror it will be useful to me

  • Hello again,

    A couple of other thoughts:-

    1) Although you have granted permissions to the Object XYZ to the Login, is there any possibility that they are in a Group that explicitly has these permissions denied?

    2) Is Object XYZ a View? If so, do all the underlying Objects belong to the same Owner?

    (Just in case these suggestion help).

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • Hi,

    Login is accessed thro a Windows Group. Rights are granted to a Windows Domain group on both the local and target server.

    There is no permission denied for these tables.

    Xyz is a table. Cross ownership issues dont arise.

    My suspicions include somethng like Windows account settings/Any delegation setting/

    or some distrubuted transaction issue..

    Has anyone faced this error before? If yes please drop in your suggestion..

    Regards,

    S.V.Nagaraj

  • I actually ran into this this morning as well. As a work around I was able to grant select on the tables on the linked server, not something I'm fond of but its working now. When I find a better solution I'll try to remember to post it.

  • Hi. This is a bit of an old thread, but this error happened to me today. And I confirmed that it is caused by missing the select permission on the second side of the linked-server. If you map every login used on the linked-server this can be avoided, but if you use Windows Authentication or "map the corresponding login" you may end up passing a login without the correct permissions and not notice it. Just give the permission and the error will go away.

    It is not the most user-friendly message, but as it seems the linked-server feature needs to schema_lock every object on select...

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply