May 31, 2009 at 4:45 am
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
Regards,
Raj
May 31, 2009 at 4:47 am
typo..
Problem occured when we moved from SQL to Windows authentication
Regards,
Raj
May 31, 2009 at 5:59 pm
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
May 31, 2009 at 8:08 pm
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
Regards,
Raj
May 31, 2009 at 9:37 pm
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
June 1, 2009 at 12:05 am
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..
Regards,
Raj
June 1, 2009 at 12:07 am
Err typo..
Even if u throw a few possiblities on when I get such a errror it will be useful to me
Regards,
Raj
June 1, 2009 at 7:38 am
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
June 1, 2009 at 8:01 am
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
Regards,
Raj
January 28, 2011 at 9:01 am
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.
April 17, 2013 at 6:52 pm
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