schema lock permission denied

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » schema lock permission denied

11 posts, Page 1 of 2

1 2 »»

schema lock permission denied

Rate Topic

Display Mode

Topic Options

Author

Message

arr.nagaraj

Posted Sunday, May 31, 2009 4:45 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

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

Strictlysql.blogspot.com

Post #726312

arr.nagaraj

Posted Sunday, May 31, 2009 4:47 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

typo..

Problem occured when we moved from SQL to Windows authentication

Regards,
Raj

Strictlysql.blogspot.com

Post #726313

John H Marsh

Posted Sunday, May 31, 2009 5:59 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 28, 2013 1:54 AM
Points: 1,325, Visits: 1,376

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

Post #726383

arr.nagaraj

Posted Sunday, May 31, 2009 8:08 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

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

Strictlysql.blogspot.com

Post #726388

John H Marsh

Posted Sunday, May 31, 2009 9:37 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 28, 2013 1:54 AM
Points: 1,325, Visits: 1,376

Hello again,

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

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Post #726397

arr.nagaraj

Posted Monday, June 01, 2009 12:05 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

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

Strictlysql.blogspot.com

Post #726427

arr.nagaraj

Posted Monday, June 01, 2009 12:07 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

Err typo..

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

Regards,
Raj

Strictlysql.blogspot.com

Post #726428

John H Marsh

Posted Monday, June 01, 2009 7:38 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 28, 2013 1:54 AM
Points: 1,325, Visits: 1,376

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

Post #726650

arr.nagaraj

Posted Monday, June 01, 2009 8:01 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

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

Strictlysql.blogspot.com

Post #726676

sean hawkes

Posted Friday, January 28, 2011 9:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, March 28, 2013 2:50 PM
Points: 27, Visits: 348

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.

Post #1055382

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions