Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

schema lock permission denied Expand / Collapse
Author
Message
Posted Sunday, May 31, 2009 4:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 4:02 AM
Points: 488, Visits: 1,340
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
Posted Sunday, May 31, 2009 4:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 4:02 AM
Points: 488, Visits: 1,340

typo..

Problem occured when we moved from SQL to Windows authentication


Regards,
Raj

Strictlysql.blogspot.com
Post #726313
Posted Sunday, May 31, 2009 5:59 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
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
Posted Sunday, May 31, 2009 8:08 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 4:02 AM
Points: 488, Visits: 1,340
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
Posted Sunday, May 31, 2009 9:37 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
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
Post #726397
Posted Monday, June 1, 2009 12:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 4:02 AM
Points: 488, Visits: 1,340
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
Posted Monday, June 1, 2009 12:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 4:02 AM
Points: 488, Visits: 1,340
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
Posted Monday, June 1, 2009 7:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
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
Posted Monday, June 1, 2009 8:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 4:02 AM
Points: 488, Visits: 1,340
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
Posted Friday, January 28, 2011 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 25, 2013 5:29 AM
Points: 28, Visits: 355
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 »

Add to briefcase 12»»

Permissions Expand / Collapse