Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


schema lock permission denied


schema lock permission denied

Author
Message
arr.nagaraj
arr.nagaraj
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 1588
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

http://Strictlysql.blogspot.com
arr.nagaraj
arr.nagaraj
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 1588
typo..

Problem occured when we moved from SQL to Windows authentication

Regards,
Raj

http://Strictlysql.blogspot.com
John H Marsh
John H Marsh
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1341 Visits: 1455
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
arr.nagaraj
arr.nagaraj
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 1588
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

http://Strictlysql.blogspot.com
John H Marsh
John H Marsh
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1341 Visits: 1455
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
arr.nagaraj
arr.nagaraj
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 1588
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

http://Strictlysql.blogspot.com
arr.nagaraj
arr.nagaraj
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 1588
Err typo..

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

Regards,
Raj

http://Strictlysql.blogspot.com
John H Marsh
John H Marsh
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1341 Visits: 1455
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
arr.nagaraj
arr.nagaraj
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 1588
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

http://Strictlysql.blogspot.com
sean hawkes
sean hawkes
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 357
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search