View of Linked Server

Jim,

Does it work and fail for the same person? Or for some persons it always
works and for others it always fails? This would suggest a permissions
issue on the server to which you are linking. If so, make sure that all
users of the linked server have the needed permissions on that other server.

If that is no help, could you script out your linked server definition and
linked server login definitions? In SSMS right click on the linked server
name and Script Linked Server as \ CREATE to

Feel free to change names, but please do not hide anything else.

RLF


"Jim" wrote in message

> Corrected error message:
>
> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
> The SCHEMA LOCK permission was denied on the object 'table1',
> database 'db1', schema 'dbo'.
>
>
> "Jim" wrote in message
>
>> Good day!
>>
>> This much works fine:
>>
>> sp_addlinkedserver 'serverB'
>> go
>> create view [dbo].[serverB_db1_table1]
>> as
>> select * from [serverB].[db1].[dbo].[table1]
>>
>>
>>
>> But querying the view
>>
>> select top 10 * from serverB_db1_table1
>>
>> might work fine, or it might return the error
>>
>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>
>> The SCHEMA LOCK permission was denied on the object 'blast_hsp_best',
>> database 'vgd_blast', schema 'dbo'.
>>
>>
>>
>> Any ideas out there?
>>
>>
>> Thanks,
>> Jim
>>
>>
>>
>
>
 >> Stay informed about: View of Linked Server 

Russell,

Thank you for the reply.

It does work and fail for the same person.

Forgive my newness to linked servers. I am not finding the place in SQL
Server Mgmt Studio to right-click and get:
"Script Linked Server as \ CREATE to"
Should I be able to accomplish that in Object Explorer after connecting to
that SQL Server and right-clicking on that server's name (top level of
tree)?

By the way, this has worked:
sp_addlinkedserver 'serverB'
go

And, at times, I have been able to query serverB via the local view.

Jim

"Russell Fields" wrote in message

> Jim,
>
> Does it work and fail for the same person? Or for some persons it always
> works and for others it always fails? This would suggest a permissions
> issue on the server to which you are linking. If so, make sure that all
> users of the linked server have the needed permissions on that other
> server.
>
> If that is no help, could you script out your linked server definition and
> linked server login definitions? In SSMS right click on the linked server
> name and Script Linked Server as \ CREATE to
>
> Feel free to change names, but please do not hide anything else.
>
> RLF
>
>
> "Jim" wrote in message
>
>> Corrected error message:
>>
>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>> The SCHEMA LOCK permission was denied on the object 'table1',
>> database 'db1', schema 'dbo'.
>>
>>
>> "Jim" wrote in message
>>
>>> Good day!
>>>
>>> This much works fine:
>>>
>>> sp_addlinkedserver 'serverB'
>>> go
>>> create view [dbo].[serverB_db1_table1]
>>> as
>>> select * from [serverB].[db1].[dbo].[table1]
>>>
>>>
>>>
>>> But querying the view
>>>
>>> select top 10 * from serverB_db1_table1
>>>
>>> might work fine, or it might return the error
>>>
>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>
>>> The SCHEMA LOCK permission was denied on the object 'blast_hsp_best',
>>> database 'vgd_blast', schema 'dbo'.
>>>
>>>
>>>
>>> Any ideas out there?
>>>
>>>
>>> Thanks,
>>> Jim
>>>
>>>
>>>
>>
>>
>
 >> Stay informed about: View of Linked Server 

Found it!

/****** Object: LinkedServer [serverB] Script Date: 02/19/2009 14:21:27
******/
EXEC master.dbo.sp_addlinkedserver @server = N'serverB', @srvproduct=N'SQL
Server'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'serverB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'collation
compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'data access',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'dist',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'pub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc out',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'sub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'connect
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'collation
name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'lazy schema
validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'query
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'use remote
collation', @optvalue=N'true'


"Russell Fields" wrote in message

> Jim,
>
> Does it work and fail for the same person? Or for some persons it always
> works and for others it always fails? This would suggest a permissions
> issue on the server to which you are linking. If so, make sure that all
> users of the linked server have the needed permissions on that other
> server.
>
> If that is no help, could you script out your linked server definition and
> linked server login definitions? In SSMS right click on the linked server
> name and Script Linked Server as \ CREATE to
>
> Feel free to change names, but please do not hide anything else.
>
> RLF
>
>
> "Jim" wrote in message
>
>> Corrected error message:
>>
>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>> The SCHEMA LOCK permission was denied on the object 'table1',
>> database 'db1', schema 'dbo'.
>>
>>
>> "Jim" wrote in message
>>
>>> Good day!
>>>
>>> This much works fine:
>>>
>>> sp_addlinkedserver 'serverB'
>>> go
>>> create view [dbo].[serverB_db1_table1]
>>> as
>>> select * from [serverB].[db1].[dbo].[table1]
>>>
>>>
>>>
>>> But querying the view
>>>
>>> select top 10 * from serverB_db1_table1
>>>
>>> might work fine, or it might return the error
>>>
>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>
>>> The SCHEMA LOCK permission was denied on the object 'blast_hsp_best',
>>> database 'vgd_blast', schema 'dbo'.
>>>
>>>
>>>
>>> Any ideas out there?
>>>
>>>
>>> Thanks,
>>> Jim
>>>
>>>
>>>
>>
>>
>
 >> Stay informed about: View of Linked Server 

Jim,

I have several linked servers with almost identical definitions, including
all the sp_serveroption settings.

I don't user @srvproduct=N'SQL Server' but I use @provider=N'SQLNCLI'
because that lets me name my linked server with an alias, rather than the
physical server name. (Sample below.) But I do NOT think that this has
anything to do with your problem.

EXEC master.dbo.sp_addlinkedserver @server = N'LinkB', @provider=N'SQLNCLI',
@srvproduct=N'ServerB', @datasrc=N'ServerB', @catalog=N'SomeDB'

It definitely seems that you are having security / authentication problems.
My first comment would be "Make sure everybody has rights" but I would have
to add "all the time" since it works sometimes for you. So, some more
questions:

Does this always succeed if you are logged onto the SQL Server, but never
when you logged onto another computer? If so, then your Kerberos settings
are probably missing or at fault and you may need a domain admin to fix
them.

Does it sometimes work and sometimes not even logged onto your own computer
and not the SQL Server? Then I would wonder if something is being done on
either the Other SQL Server or on the domain that is changing permissions.
That will be harder to find, but you could try timings and see if that
correlates with anything.

Sorry I cannot see a clear picture, but hope this moves you forward a step.

RLF


"Jim" wrote in message

> Found it!
>
> /****** Object: LinkedServer [serverB] Script Date: 02/19/2009
> 14:21:27 ******/
> EXEC master.dbo.sp_addlinkedserver @server = N'serverB', @srvproduct=N'SQL
> Server'
> /* For security reasons the linked server remote logins password is
> changed with ######## */
> EXEC master.dbo.sp_addlinkedsrvlogin
> @rmtsrvname=N'serverB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
>
> GO
> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'collation
> compatible', @optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'data
> access', @optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'dist',
> @optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'pub',
> @optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc',
> @optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc out',
> @optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'sub',
> @optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'connect
> timeout', @optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'collation
> name', @optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'lazy schema
> validation', @optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'query
> timeout', @optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'use remote
> collation', @optvalue=N'true'
>
>
> "Russell Fields" wrote in message
>
>> Jim,
>>
>> Does it work and fail for the same person? Or for some persons it always
>> works and for others it always fails? This would suggest a permissions
>> issue on the server to which you are linking. If so, make sure that all
>> users of the linked server have the needed permissions on that other
>> server.
>>
>> If that is no help, could you script out your linked server definition
>> and linked server login definitions? In SSMS right click on the linked
>> server name and Script Linked Server as \ CREATE to
>>
>> Feel free to change names, but please do not hide anything else.
>>
>> RLF
>>
>>
>> "Jim" wrote in message
>>
>>> Corrected error message:
>>>
>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>> The SCHEMA LOCK permission was denied on the object 'table1',
>>> database 'db1', schema 'dbo'.
>>>
>>>
>>> "Jim" wrote in message
>>>
>>>> Good day!
>>>>
>>>> This much works fine:
>>>>
>>>> sp_addlinkedserver 'serverB'
>>>> go
>>>> create view [dbo].[serverB_db1_table1]
>>>> as
>>>> select * from [serverB].[db1].[dbo].[table1]
>>>>
>>>>
>>>>
>>>> But querying the view
>>>>
>>>> select top 10 * from serverB_db1_table1
>>>>
>>>> might work fine, or it might return the error
>>>>
>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>>
>>>> The SCHEMA LOCK permission was denied on the object 'blast_hsp_best',
>>>> database 'vgd_blast', schema 'dbo'.
>>>>
>>>>
>>>>
>>>> Any ideas out there?
>>>>
>>>>
>>>> Thanks,
>>>> Jim
>>>>
>>>>
>>>>
>>>
>>>
>>
>
>
 >> Stay informed about: View of Linked Server 

Russell,

When it runs fine, it runs fine--through SSMS on my desktop PC and through
SSMS on the host server. When it gives the error, it gives the error via
both interfaces. At any given time, the query runs fine from both
interfaces, or it errs in the same manner from both places. I am the admin
on the SQL Server, and no one else is accessing it at all. It seems that
something in the SQL Server is switching.

I am tracing SQL Server A and SQL Server B, to see if there are any clues.
Right now the query is not working, so the error message is:

Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
The SCHEMA LOCK permission was denied on the object 'table1', database
'db1', schema 'dbo'.

and the trace on the linked server, among other things, shows:

exec [sys].sp_getschemalock @p1 output,@p2 output,N'"db1"."dbo"."table1"'

Jim


"Russell Fields" wrote in message

> Jim,
>
> I have several linked servers with almost identical definitions, including
> all the sp_serveroption settings.
>
> I don't user @srvproduct=N'SQL Server' but I use @provider=N'SQLNCLI'
> because that lets me name my linked server with an alias, rather than the
> physical server name. (Sample below.) But I do NOT think that this has
> anything to do with your problem.
>
> EXEC master.dbo.sp_addlinkedserver @server = N'LinkB',
> @provider=N'SQLNCLI', @srvproduct=N'ServerB', @datasrc=N'ServerB',
> @catalog=N'SomeDB'
>
> It definitely seems that you are having security / authentication
> problems. My first comment would be "Make sure everybody has rights" but I
> would have to add "all the time" since it works sometimes for you. So,
> some more questions:
>
> Does this always succeed if you are logged onto the SQL Server, but never
> when you logged onto another computer? If so, then your Kerberos settings
> are probably missing or at fault and you may need a domain admin to fix
> them.
>
> Does it sometimes work and sometimes not even logged onto your own
> computer and not the SQL Server? Then I would wonder if something is
> being done on either the Other SQL Server or on the domain that is
> changing permissions. That will be harder to find, but you could try
> timings and see if that correlates with anything.
>
> Sorry I cannot see a clear picture, but hope this moves you forward a
> step.
>
> RLF
>
>
> "Jim" wrote in message
>
>> Found it!
>>
>> /****** Object: LinkedServer [serverB] Script Date: 02/19/2009
>> 14:21:27 ******/
>> EXEC master.dbo.sp_addlinkedserver @server = N'serverB',
>> @srvproduct=N'SQL Server'
>> /* For security reasons the linked server remote logins password is
>> changed with ######## */
>> EXEC master.dbo.sp_addlinkedsrvlogin
>> @rmtsrvname=N'serverB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
>>
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'collation
>> compatible', @optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'data
>> access', @optvalue=N'true'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'dist',
>> @optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'pub',
>> @optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc',
>> @optvalue=N'true'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc out',
>> @optvalue=N'true'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'sub',
>> @optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'connect
>> timeout', @optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'collation
>> name', @optvalue=null
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'lazy
>> schema validation', @optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'query
>> timeout', @optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'use remote
>> collation', @optvalue=N'true'
>>
>>
>> "Russell Fields" wrote in message
>>
>>> Jim,
>>>
>>> Does it work and fail for the same person? Or for some persons it
>>> always works and for others it always fails? This would suggest a
>>> permissions issue on the server to which you are linking. If so, make
>>> sure that all users of the linked server have the needed permissions on
>>> that other server.
>>>
>>> If that is no help, could you script out your linked server definition
>>> and linked server login definitions? In SSMS right click on the linked
>>> server name and Script Linked Server as \ CREATE to
>>>
>>> Feel free to change names, but please do not hide anything else.
>>>
>>> RLF
>>>
>>>
>>> "Jim" wrote in message
>>>
>>>> Corrected error message:
>>>>
>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>> The SCHEMA LOCK permission was denied on the object 'table1',
>>>> database 'db1', schema 'dbo'.
>>>>
>>>>
>>>> "Jim" wrote in message
>>>>
>>>>> Good day!
>>>>>
>>>>> This much works fine:
>>>>>
>>>>> sp_addlinkedserver 'serverB'
>>>>> go
>>>>> create view [dbo].[serverB_db1_table1]
>>>>> as
>>>>> select * from [serverB].[db1].[dbo].[table1]
>>>>>
>>>>>
>>>>>
>>>>> But querying the view
>>>>>
>>>>> select top 10 * from serverB_db1_table1
>>>>>
>>>>> might work fine, or it might return the error
>>>>>
>>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>>>
>>>>> The SCHEMA LOCK permission was denied on the object 'blast_hsp_best',
>>>>> database 'vgd_blast', schema 'dbo'.
>>>>>
>>>>>
>>>>>
>>>>> Any ideas out there?
>>>>>
>>>>>
>>>>> Thanks,
>>>>> Jim
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
>
 >> Stay informed about: View of Linked Server 

The same behavior is experienced today. Earlier statements should be
refined, however. There are periods of time when the query runs fine, and
there are periods of time when the query errs with:
Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
The SCHEMA LOCK permission was denied on the object 'blast_hsp_best',
database 'vgd_blast', schema 'dbo'.

Furthermore, the periods time during which the query errs are significantly
longer than the periods of time when the query is running correctly.


"Russell Fields" wrote in message

> Jim,
>
> I have several linked servers with almost identical definitions, including
> all the sp_serveroption settings.
>
> I don't user @srvproduct=N'SQL Server' but I use @provider=N'SQLNCLI'
> because that lets me name my linked server with an alias, rather than the
> physical server name. (Sample below.) But I do NOT think that this has
> anything to do with your problem.
>
> EXEC master.dbo.sp_addlinkedserver @server = N'LinkB',
> @provider=N'SQLNCLI', @srvproduct=N'ServerB', @datasrc=N'ServerB',
> @catalog=N'SomeDB'
>
> It definitely seems that you are having security / authentication
> problems. My first comment would be "Make sure everybody has rights" but I
> would have to add "all the time" since it works sometimes for you. So,
> some more questions:
>
> Does this always succeed if you are logged onto the SQL Server, but never
> when you logged onto another computer? If so, then your Kerberos settings
> are probably missing or at fault and you may need a domain admin to fix
> them.
>
> Does it sometimes work and sometimes not even logged onto your own
> computer and not the SQL Server? Then I would wonder if something is
> being done on either the Other SQL Server or on the domain that is
> changing permissions. That will be harder to find, but you could try
> timings and see if that correlates with anything.
>
> Sorry I cannot see a clear picture, but hope this moves you forward a
> step.
>
> RLF
>
>
> "Jim" wrote in message
>
>> Found it!
>>
>> /****** Object: LinkedServer [serverB] Script Date: 02/19/2009
>> 14:21:27 ******/
>> EXEC master.dbo.sp_addlinkedserver @server = N'serverB',
>> @srvproduct=N'SQL Server'
>> /* For security reasons the linked server remote logins password is
>> changed with ######## */
>> EXEC master.dbo.sp_addlinkedsrvlogin
>> @rmtsrvname=N'serverB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
>>
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'collation
>> compatible', @optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'data
>> access', @optvalue=N'true'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'dist',
>> @optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'pub',
>> @optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc',
>> @optvalue=N'true'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc out',
>> @optvalue=N'true'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'sub',
>> @optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'connect
>> timeout', @optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'collation
>> name', @optvalue=null
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'lazy
>> schema validation', @optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'query
>> timeout', @optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'use remote
>> collation', @optvalue=N'true'
>>
>>
>> "Russell Fields" wrote in message
>>
>>> Jim,
>>>
>>> Does it work and fail for the same person? Or for some persons it
>>> always works and for others it always fails? This would suggest a
>>> permissions issue on the server to which you are linking. If so, make
>>> sure that all users of the linked server have the needed permissions on
>>> that other server.
>>>
>>> If that is no help, could you script out your linked server definition
>>> and linked server login definitions? In SSMS right click on the linked
>>> server name and Script Linked Server as \ CREATE to
>>>
>>> Feel free to change names, but please do not hide anything else.
>>>
>>> RLF
>>>
>>>
>>> "Jim" wrote in message
>>>
>>>> Corrected error message:
>>>>
>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>> The SCHEMA LOCK permission was denied on the object 'table1',
>>>> database 'db1', schema 'dbo'.
>>>>
>>>>
>>>> "Jim" wrote in message
>>>>
>>>>> Good day!
>>>>>
>>>>> This much works fine:
>>>>>
>>>>> sp_addlinkedserver 'serverB'
>>>>> go
>>>>> create view [dbo].[serverB_db1_table1]
>>>>> as
>>>>> select * from [serverB].[db1].[dbo].[table1]
>>>>>
>>>>>
>>>>>
>>>>> But querying the view
>>>>>
>>>>> select top 10 * from serverB_db1_table1
>>>>>
>>>>> might work fine, or it might return the error
>>>>>
>>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>>>
>>>>> The SCHEMA LOCK permission was denied on the object 'blast_hsp_best',
>>>>> database 'vgd_blast', schema 'dbo'.
>>>>>
>>>>>
>>>>>
>>>>> Any ideas out there?
>>>>>
>>>>>
>>>>> Thanks,
>>>>> Jim
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
>
 >> Stay informed about: View of Linked Server 

Jim,

I spent some time looking at this today, but it is hard to understand, so I
have nothing definitive. The fact that it is periods of time removes focus
from and user and focuses on the server(s). If you Google for this problem,
you will see ours is the longest discussion of this topic. (Which means
that I have not been a lot of help so far.)

The sp_getschemalock is being issued by the server (not your code) as a part
of the linked server communication. This implies that the SQL Server
service account is being blocked at times. Usually my experience with
similar types of problems is the authentication not being Kerberos, or
something is messing up the Kerberson security. (But this is NOT my domain
of expertise.)

Is your SQL Server service running as Local System or as a domain account?
It should be a domain account for reliable network communications to other
servers. (But it probably already is.)

I am away this weekend, but I will try to look back in on Monday.

RLF



"Jim" wrote in message

> The same behavior is experienced today. Earlier statements should be
> refined, however. There are periods of time when the query runs fine, and
> there are periods of time when the query errs with:
> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
> The SCHEMA LOCK permission was denied on the object 'blast_hsp_best',
> database 'vgd_blast', schema 'dbo'.
>
> Furthermore, the periods time during which the query errs are
> significantly longer than the periods of time when the query is running
> correctly.
>
>
> "Russell Fields" wrote in message
>
>> Jim,
>>
>> I have several linked servers with almost identical definitions,
>> including all the sp_serveroption settings.
>>
>> I don't user @srvproduct=N'SQL Server' but I use @provider=N'SQLNCLI'
>> because that lets me name my linked server with an alias, rather than the
>> physical server name. (Sample below.) But I do NOT think that this has
>> anything to do with your problem.
>>
>> EXEC master.dbo.sp_addlinkedserver @server = N'LinkB',
>> @provider=N'SQLNCLI', @srvproduct=N'ServerB', @datasrc=N'ServerB',
>> @catalog=N'SomeDB'
>>
>> It definitely seems that you are having security / authentication
>> problems. My first comment would be "Make sure everybody has rights" but
>> I would have to add "all the time" since it works sometimes for you. So,
>> some more questions:
>>
>> Does this always succeed if you are logged onto the SQL Server, but never
>> when you logged onto another computer? If so, then your Kerberos
>> settings are probably missing or at fault and you may need a domain admin
>> to fix them.
>>
>> Does it sometimes work and sometimes not even logged onto your own
>> computer and not the SQL Server? Then I would wonder if something is
>> being done on either the Other SQL Server or on the domain that is
>> changing permissions. That will be harder to find, but you could try
>> timings and see if that correlates with anything.
>>
>> Sorry I cannot see a clear picture, but hope this moves you forward a
>> step.
>>
>> RLF
>>
>>
>> "Jim" wrote in message
>>
>>> Found it!
>>>
>>> /****** Object: LinkedServer [serverB] Script Date: 02/19/2009
>>> 14:21:27 ******/
>>> EXEC master.dbo.sp_addlinkedserver @server = N'serverB',
>>> @srvproduct=N'SQL Server'
>>> /* For security reasons the linked server remote logins password is
>>> changed with ######## */
>>> EXEC master.dbo.sp_addlinkedsrvlogin
>>> @rmtsrvname=N'serverB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
>>>
>>> GO
>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'collation
>>> compatible', @optvalue=N'false'
>>> GO
>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'data
>>> access', @optvalue=N'true'
>>> GO
>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'dist',
>>> @optvalue=N'false'
>>> GO
>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'pub',
>>> @optvalue=N'false'
>>> GO
>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc',
>>> @optvalue=N'true'
>>> GO
>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc out',
>>> @optvalue=N'true'
>>> GO
>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'sub',
>>> @optvalue=N'false'
>>> GO
>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'connect
>>> timeout', @optvalue=N'0'
>>> GO
>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'collation
>>> name', @optvalue=null
>>> GO
>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'lazy
>>> schema validation', @optvalue=N'false'
>>> GO
>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'query
>>> timeout', @optvalue=N'0'
>>> GO
>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'use
>>> remote collation', @optvalue=N'true'
>>>
>>>
>>> "Russell Fields" wrote in message
>>>
>>>> Jim,
>>>>
>>>> Does it work and fail for the same person? Or for some persons it
>>>> always works and for others it always fails? This would suggest a
>>>> permissions issue on the server to which you are linking. If so, make
>>>> sure that all users of the linked server have the needed permissions on
>>>> that other server.
>>>>
>>>> If that is no help, could you script out your linked server definition
>>>> and linked server login definitions? In SSMS right click on the linked
>>>> server name and Script Linked Server as \ CREATE to
>>>>
>>>> Feel free to change names, but please do not hide anything else.
>>>>
>>>> RLF
>>>>
>>>>
>>>> "Jim" wrote in message
>>>>
>>>>> Corrected error message:
>>>>>
>>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>>> The SCHEMA LOCK permission was denied on the object 'table1',
>>>>> database 'db1', schema 'dbo'.
>>>>>
>>>>>
>>>>> "Jim" wrote in message
>>>>>
>>>>>> Good day!
>>>>>>
>>>>>> This much works fine:
>>>>>>
>>>>>> sp_addlinkedserver 'serverB'
>>>>>> go
>>>>>> create view [dbo].[serverB_db1_table1]
>>>>>> as
>>>>>> select * from [serverB].[db1].[dbo].[table1]
>>>>>>
>>>>>>
>>>>>>
>>>>>> But querying the view
>>>>>>
>>>>>> select top 10 * from serverB_db1_table1
>>>>>>
>>>>>> might work fine, or it might return the error
>>>>>>
>>>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>>>>
>>>>>> The SCHEMA LOCK permission was denied on the object 'blast_hsp_best',
>>>>>> database 'vgd_blast', schema 'dbo'.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Any ideas out there?
>>>>>>
>>>>>>
>>>>>> Thanks,
>>>>>> Jim
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>
 >> Stay informed about: View of Linked Server 

Russell,

Thank you very much for continuing to revisit this thread.

The both SQL Servers are running as a domain account--same domain account
for both. Thank you for mentioning the Kerberos settings again. I will
begin to look into that aspect.

Best,
Jim

"Russell Fields" wrote in message

> Jim,
>
> I spent some time looking at this today, but it is hard to understand, so
> I have nothing definitive. The fact that it is periods of time removes
> focus from and user and focuses on the server(s). If you Google for this
> problem, you will see ours is the longest discussion of this topic.
> (Which means that I have not been a lot of help so far.)
>
> The sp_getschemalock is being issued by the server (not your code) as a
> part of the linked server communication. This implies that the SQL Server
> service account is being blocked at times. Usually my experience with
> similar types of problems is the authentication not being Kerberos, or
> something is messing up the Kerberson security. (But this is NOT my
> domain of expertise.)
>
> Is your SQL Server service running as Local System or as a domain account?
> It should be a domain account for reliable network communications to other
> servers. (But it probably already is.)
>
> I am away this weekend, but I will try to look back in on Monday.
>
> RLF
>
>
>
> "Jim" wrote in message
>
>> The same behavior is experienced today. Earlier statements should be
>> refined, however. There are periods of time when the query runs fine,
>> and there are periods of time when the query errs with:
>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>> The SCHEMA LOCK permission was denied on the object 'blast_hsp_best',
>> database 'vgd_blast', schema 'dbo'.
>>
>> Furthermore, the periods time during which the query errs are
>> significantly longer than the periods of time when the query is running
>> correctly.
>>
>>
>> "Russell Fields" wrote in message
>>
>>> Jim,
>>>
>>> I have several linked servers with almost identical definitions,
>>> including all the sp_serveroption settings.
>>>
>>> I don't user @srvproduct=N'SQL Server' but I use @provider=N'SQLNCLI'
>>> because that lets me name my linked server with an alias, rather than
>>> the physical server name. (Sample below.) But I do NOT think that this
>>> has anything to do with your problem.
>>>
>>> EXEC master.dbo.sp_addlinkedserver @server = N'LinkB',
>>> @provider=N'SQLNCLI', @srvproduct=N'ServerB', @datasrc=N'ServerB',
>>> @catalog=N'SomeDB'
>>>
>>> It definitely seems that you are having security / authentication
>>> problems. My first comment would be "Make sure everybody has rights" but
>>> I would have to add "all the time" since it works sometimes for you.
>>> So, some more questions:
>>>
>>> Does this always succeed if you are logged onto the SQL Server, but
>>> never when you logged onto another computer? If so, then your Kerberos
>>> settings are probably missing or at fault and you may need a domain
>>> admin to fix them.
>>>
>>> Does it sometimes work and sometimes not even logged onto your own
>>> computer and not the SQL Server? Then I would wonder if something is
>>> being done on either the Other SQL Server or on the domain that is
>>> changing permissions. That will be harder to find, but you could try
>>> timings and see if that correlates with anything.
>>>
>>> Sorry I cannot see a clear picture, but hope this moves you forward a
>>> step.
>>>
>>> RLF
>>>
>>>
>>> "Jim" wrote in message
>>>
>>>> Found it!
>>>>
>>>> /****** Object: LinkedServer [serverB] Script Date: 02/19/2009
>>>> 14:21:27 ******/
>>>> EXEC master.dbo.sp_addlinkedserver @server = N'serverB',
>>>> @srvproduct=N'SQL Server'
>>>> /* For security reasons the linked server remote logins password is
>>>> changed with ######## */
>>>> EXEC master.dbo.sp_addlinkedsrvlogin
>>>> @rmtsrvname=N'serverB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
>>>>
>>>> GO
>>>> EXEC master.dbo.sp_serveroption @server=N'serverB',
>>>> @optname=N'collation compatible', @optvalue=N'false'
>>>> GO
>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'data
>>>> access', @optvalue=N'true'
>>>> GO
>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'dist',
>>>> @optvalue=N'false'
>>>> GO
>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'pub',
>>>> @optvalue=N'false'
>>>> GO
>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc',
>>>> @optvalue=N'true'
>>>> GO
>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc
>>>> out', @optvalue=N'true'
>>>> GO
>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'sub',
>>>> @optvalue=N'false'
>>>> GO
>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'connect
>>>> timeout', @optvalue=N'0'
>>>> GO
>>>> EXEC master.dbo.sp_serveroption @server=N'serverB',
>>>> @optname=N'collation name', @optvalue=null
>>>> GO
>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'lazy
>>>> schema validation', @optvalue=N'false'
>>>> GO
>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'query
>>>> timeout', @optvalue=N'0'
>>>> GO
>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'use
>>>> remote collation', @optvalue=N'true'
>>>>
>>>>
>>>> "Russell Fields" wrote in message
>>>>
>>>>> Jim,
>>>>>
>>>>> Does it work and fail for the same person? Or for some persons it
>>>>> always works and for others it always fails? This would suggest a
>>>>> permissions issue on the server to which you are linking. If so, make
>>>>> sure that all users of the linked server have the needed permissions
>>>>> on that other server.
>>>>>
>>>>> If that is no help, could you script out your linked server definition
>>>>> and linked server login definitions? In SSMS right click on the
>>>>> linked server name and Script Linked Server as \ CREATE to
>>>>>
>>>>> Feel free to change names, but please do not hide anything else.
>>>>>
>>>>> RLF
>>>>>
>>>>>
>>>>> "Jim" wrote in message
>>>>>
>>>>>> Corrected error message:
>>>>>>
>>>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>>>> The SCHEMA LOCK permission was denied on the object 'table1',
>>>>>> database 'db1', schema 'dbo'.
>>>>>>
>>>>>>
>>>>>> "Jim" wrote in message
>>>>>>
>>>>>>> Good day!
>>>>>>>
>>>>>>> This much works fine:
>>>>>>>
>>>>>>> sp_addlinkedserver 'serverB'
>>>>>>> go
>>>>>>> create view [dbo].[serverB_db1_table1]
>>>>>>> as
>>>>>>> select * from [serverB].[db1].[dbo].[table1]
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> But querying the view
>>>>>>>
>>>>>>> select top 10 * from serverB_db1_table1
>>>>>>>
>>>>>>> might work fine, or it might return the error
>>>>>>>
>>>>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>>>>>
>>>>>>> The SCHEMA LOCK permission was denied on the object
>>>>>>> 'blast_hsp_best', database 'vgd_blast', schema 'dbo'.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Any ideas out there?
>>>>>>>
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Jim
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
>
 >> Stay informed about: View of Linked Server 

Jim,

Have you found a solution or a clue yet? Me either.

However, I went back to review previous problems that I faced with server
connectivity and they seem to have centered around duplicate SPNs. These
cause problems, sometimes intermittent. For example, causing the server to
fall back from Kerberos to NTLM authentication.

http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-k...eros-an

(I believe is may depend on whether the machine or account with the
duplicate SPN is currently online, but (as I said) this is not my area of
expertise.) Here are some other links on the topic:

About duplicate SPNs on the same machine.
http://support.microsoft.com/kb/832109

General Kerberos Domain problems
http://blogs.technet.com/askds/archive/2008/05/29/kerberos-authenticat...-proble
Duplicate SPN problem
http://blogs.technet.com/askds/archive/2008/06/09/kerberos-authenticat...-proble
SPN not on the correct account.
http://blogs.technet.com/askds/archive/2008/06/11/kerberos-authenticat...-proble

And, if you have Windows 2008:
http://blogs.msdn.com/psssql/archive/2009/02/13/searching-for-duplicat...pn-s-go

It may be that you do not have the rights to investigate this. If so,
perhaps this information will help you to get the proper people to
investigate for you.

RLF


"Jim" wrote in message

> Russell,
>
> Thank you very much for continuing to revisit this thread.
>
> The both SQL Servers are running as a domain account--same domain account
> for both. Thank you for mentioning the Kerberos settings again. I will
> begin to look into that aspect.
>
> Best,
> Jim
>
> "Russell Fields" wrote in message
>
>> Jim,
>>
>> I spent some time looking at this today, but it is hard to understand, so
>> I have nothing definitive. The fact that it is periods of time removes
>> focus from and user and focuses on the server(s). If you Google for this
>> problem, you will see ours is the longest discussion of this topic.
>> (Which means that I have not been a lot of help so far.)
>>
>> The sp_getschemalock is being issued by the server (not your code) as a
>> part of the linked server communication. This implies that the SQL
>> Server service account is being blocked at times. Usually my experience
>> with similar types of problems is the authentication not being Kerberos,
>> or something is messing up the Kerberson security. (But this is NOT my
>> domain of expertise.)
>>
>> Is your SQL Server service running as Local System or as a domain
>> account? It should be a domain account for reliable network
>> communications to other servers. (But it probably already is.)
>>
>> I am away this weekend, but I will try to look back in on Monday.
>>
>> RLF
>>
>>
>>
>> "Jim" wrote in message
>>
>>> The same behavior is experienced today. Earlier statements should be
>>> refined, however. There are periods of time when the query runs fine,
>>> and there are periods of time when the query errs with:
>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>> The SCHEMA LOCK permission was denied on the object 'blast_hsp_best',
>>> database 'vgd_blast', schema 'dbo'.
>>>
>>> Furthermore, the periods time during which the query errs are
>>> significantly longer than the periods of time when the query is running
>>> correctly.
>>>
>>>
>>> "Russell Fields" wrote in message
>>>
>>>> Jim,
>>>>
>>>> I have several linked servers with almost identical definitions,
>>>> including all the sp_serveroption settings.
>>>>
>>>> I don't user @srvproduct=N'SQL Server' but I use @provider=N'SQLNCLI'
>>>> because that lets me name my linked server with an alias, rather than
>>>> the physical server name. (Sample below.) But I do NOT think that this
>>>> has anything to do with your problem.
>>>>
>>>> EXEC master.dbo.sp_addlinkedserver @server = N'LinkB',
>>>> @provider=N'SQLNCLI', @srvproduct=N'ServerB', @datasrc=N'ServerB',
>>>> @catalog=N'SomeDB'
>>>>
>>>> It definitely seems that you are having security / authentication
>>>> problems. My first comment would be "Make sure everybody has rights"
>>>> but I would have to add "all the time" since it works sometimes for
>>>> you. So, some more questions:
>>>>
>>>> Does this always succeed if you are logged onto the SQL Server, but
>>>> never when you logged onto another computer? If so, then your Kerberos
>>>> settings are probably missing or at fault and you may need a domain
>>>> admin to fix them.
>>>>
>>>> Does it sometimes work and sometimes not even logged onto your own
>>>> computer and not the SQL Server? Then I would wonder if something is
>>>> being done on either the Other SQL Server or on the domain that is
>>>> changing permissions. That will be harder to find, but you could try
>>>> timings and see if that correlates with anything.
>>>>
>>>> Sorry I cannot see a clear picture, but hope this moves you forward a
>>>> step.
>>>>
>>>> RLF
>>>>
>>>>
>>>> "Jim" wrote in message
>>>>
>>>>> Found it!
>>>>>
>>>>> /****** Object: LinkedServer [serverB] Script Date: 02/19/2009
>>>>> 14:21:27 ******/
>>>>> EXEC master.dbo.sp_addlinkedserver @server = N'serverB',
>>>>> @srvproduct=N'SQL Server'
>>>>> /* For security reasons the linked server remote logins password is
>>>>> changed with ######## */
>>>>> EXEC master.dbo.sp_addlinkedsrvlogin
>>>>> @rmtsrvname=N'serverB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
>>>>>
>>>>> GO
>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB',
>>>>> @optname=N'collation compatible', @optvalue=N'false'
>>>>> GO
>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'data
>>>>> access', @optvalue=N'true'
>>>>> GO
>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'dist',
>>>>> @optvalue=N'false'
>>>>> GO
>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'pub',
>>>>> @optvalue=N'false'
>>>>> GO
>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc',
>>>>> @optvalue=N'true'
>>>>> GO
>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc
>>>>> out', @optvalue=N'true'
>>>>> GO
>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'sub',
>>>>> @optvalue=N'false'
>>>>> GO
>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'connect
>>>>> timeout', @optvalue=N'0'
>>>>> GO
>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB',
>>>>> @optname=N'collation name', @optvalue=null
>>>>> GO
>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'lazy
>>>>> schema validation', @optvalue=N'false'
>>>>> GO
>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'query
>>>>> timeout', @optvalue=N'0'
>>>>> GO
>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'use
>>>>> remote collation', @optvalue=N'true'
>>>>>
>>>>>
>>>>> "Russell Fields" wrote in message
>>>>>
>>>>>> Jim,
>>>>>>
>>>>>> Does it work and fail for the same person? Or for some persons it
>>>>>> always works and for others it always fails? This would suggest a
>>>>>> permissions issue on the server to which you are linking. If so,
>>>>>> make sure that all users of the linked server have the needed
>>>>>> permissions on that other server.
>>>>>>
>>>>>> If that is no help, could you script out your linked server
>>>>>> definition and linked server login definitions? In SSMS right click
>>>>>> on the linked server name and Script Linked Server as \ CREATE to
>>>>>>
>>>>>> Feel free to change names, but please do not hide anything else.
>>>>>>
>>>>>> RLF
>>>>>>
>>>>>>
>>>>>> "Jim" wrote in message
>>>>>>
>>>>>>> Corrected error message:
>>>>>>>
>>>>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>>>>> The SCHEMA LOCK permission was denied on the object 'table1',
>>>>>>> database 'db1', schema 'dbo'.
>>>>>>>
>>>>>>>
>>>>>>> "Jim" wrote in message
>>>>>>>
>>>>>>>> Good day!
>>>>>>>>
>>>>>>>> This much works fine:
>>>>>>>>
>>>>>>>> sp_addlinkedserver 'serverB'
>>>>>>>> go
>>>>>>>> create view [dbo].[serverB_db1_table1]
>>>>>>>> as
>>>>>>>> select * from [serverB].[db1].[dbo].[table1]
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> But querying the view
>>>>>>>>
>>>>>>>> select top 10 * from serverB_db1_table1
>>>>>>>>
>>>>>>>> might work fine, or it might return the error
>>>>>>>>
>>>>>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>>>>>>
>>>>>>>> The SCHEMA LOCK permission was denied on the object
>>>>>>>> 'blast_hsp_best', database 'vgd_blast', schema 'dbo'.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Any ideas out there?
>>>>>>>>
>>>>>>>>
>>>>>>>> Thanks,
>>>>>>>> Jim
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>
 >> Stay informed about: View of Linked Server 

Russell,

I have not found a solution yet. I have not investigated further this
week.... other higher priorities.

Thank you for all lof the pointers. When possible, I will investigate in
those directions. In the meantime, if you have any additional ideas, please
feel free to post!

Russell, all of your attention to this issue is most appreciated!

Jim

"Russell Fields" wrote in message

> Jim,
>
> Have you found a solution or a clue yet? Me either.
>
> However, I went back to review previous problems that I faced with server
> connectivity and they seem to have centered around duplicate SPNs. These
> cause problems, sometimes intermittent. For example, causing the server
> to fall back from Kerberos to NTLM authentication.
>
> http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-k...eros-an
>
> (I believe is may depend on whether the machine or account with the
> duplicate SPN is currently online, but (as I said) this is not my area of
> expertise.) Here are some other links on the topic:
>
> About duplicate SPNs on the same machine.
> http://support.microsoft.com/kb/832109
>
> General Kerberos Domain problems
> http://blogs.technet.com/askds/archive/2008/05/29/kerberos-authenticat...-proble
> Duplicate SPN problem
> http://blogs.technet.com/askds/archive/2008/06/09/kerberos-authenticat...-proble
> SPN not on the correct account.
> http://blogs.technet.com/askds/archive/2008/06/11/kerberos-authenticat...-proble
>
> And, if you have Windows 2008:
> http://blogs.msdn.com/psssql/archive/2009/02/13/searching-for-duplicat...pn-s-go
>
> It may be that you do not have the rights to investigate this. If so,
> perhaps this information will help you to get the proper people to
> investigate for you.
>
> RLF
>
>
> "Jim" wrote in message
>
>> Russell,
>>
>> Thank you very much for continuing to revisit this thread.
>>
>> The both SQL Servers are running as a domain account--same domain account
>> for both. Thank you for mentioning the Kerberos settings again. I will
>> begin to look into that aspect.
>>
>> Best,
>> Jim
>>
>> "Russell Fields" wrote in message
>>
>>> Jim,
>>>
>>> I spent some time looking at this today, but it is hard to understand,
>>> so I have nothing definitive. The fact that it is periods of time
>>> removes focus from and user and focuses on the server(s). If you Google
>>> for this problem, you will see ours is the longest discussion of this
>>> topic. (Which means that I have not been a lot of help so far.)
>>>
>>> The sp_getschemalock is being issued by the server (not your code) as a
>>> part of the linked server communication. This implies that the SQL
>>> Server service account is being blocked at times. Usually my experience
>>> with similar types of problems is the authentication not being Kerberos,
>>> or something is messing up the Kerberson security. (But this is NOT my
>>> domain of expertise.)
>>>
>>> Is your SQL Server service running as Local System or as a domain
>>> account? It should be a domain account for reliable network
>>> communications to other servers. (But it probably already is.)
>>>
>>> I am away this weekend, but I will try to look back in on Monday.
>>>
>>> RLF
>>>
>>>
>>>
>>> "Jim" wrote in message
>>>
>>>> The same behavior is experienced today. Earlier statements should be
>>>> refined, however. There are periods of time when the query runs fine,
>>>> and there are periods of time when the query errs with:
>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>> The SCHEMA LOCK permission was denied on the object 'blast_hsp_best',
>>>> database 'vgd_blast', schema 'dbo'.
>>>>
>>>> Furthermore, the periods time during which the query errs are
>>>> significantly longer than the periods of time when the query is running
>>>> correctly.
>>>>
>>>>
>>>> "Russell Fields" wrote in message
>>>>
>>>>> Jim,
>>>>>
>>>>> I have several linked servers with almost identical definitions,
>>>>> including all the sp_serveroption settings.
>>>>>
>>>>> I don't user @srvproduct=N'SQL Server' but I use @provider=N'SQLNCLI'
>>>>> because that lets me name my linked server with an alias, rather than
>>>>> the physical server name. (Sample below.) But I do NOT think that
>>>>> this has anything to do with your problem.
>>>>>
>>>>> EXEC master.dbo.sp_addlinkedserver @server = N'LinkB',
>>>>> @provider=N'SQLNCLI', @srvproduct=N'ServerB', @datasrc=N'ServerB',
>>>>> @catalog=N'SomeDB'
>>>>>
>>>>> It definitely seems that you are having security / authentication
>>>>> problems. My first comment would be "Make sure everybody has rights"
>>>>> but I would have to add "all the time" since it works sometimes for
>>>>> you. So, some more questions:
>>>>>
>>>>> Does this always succeed if you are logged onto the SQL Server, but
>>>>> never when you logged onto another computer? If so, then your
>>>>> Kerberos settings are probably missing or at fault and you may need a
>>>>> domain admin to fix them.
>>>>>
>>>>> Does it sometimes work and sometimes not even logged onto your own
>>>>> computer and not the SQL Server? Then I would wonder if something is
>>>>> being done on either the Other SQL Server or on the domain that is
>>>>> changing permissions. That will be harder to find, but you could try
>>>>> timings and see if that correlates with anything.
>>>>>
>>>>> Sorry I cannot see a clear picture, but hope this moves you forward a
>>>>> step.
>>>>>
>>>>> RLF
>>>>>
>>>>>
>>>>> "Jim" wrote in message
>>>>>
>>>>>> Found it!
>>>>>>
>>>>>> /****** Object: LinkedServer [serverB] Script Date: 02/19/2009
>>>>>> 14:21:27 ******/
>>>>>> EXEC master.dbo.sp_addlinkedserver @server = N'serverB',
>>>>>> @srvproduct=N'SQL Server'
>>>>>> /* For security reasons the linked server remote logins password is
>>>>>> changed with ######## */
>>>>>> EXEC master.dbo.sp_addlinkedsrvlogin
>>>>>> @rmtsrvname=N'serverB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
>>>>>>
>>>>>> GO
>>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB',
>>>>>> @optname=N'collation compatible', @optvalue=N'false'
>>>>>> GO
>>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'data
>>>>>> access', @optvalue=N'true'
>>>>>> GO
>>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'dist',
>>>>>> @optvalue=N'false'
>>>>>> GO
>>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'pub',
>>>>>> @optvalue=N'false'
>>>>>> GO
>>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc',
>>>>>> @optvalue=N'true'
>>>>>> GO
>>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'rpc
>>>>>> out', @optvalue=N'true'
>>>>>> GO
>>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'sub',
>>>>>> @optvalue=N'false'
>>>>>> GO
>>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB',
>>>>>> @optname=N'connect timeout', @optvalue=N'0'
>>>>>> GO
>>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB',
>>>>>> @optname=N'collation name', @optvalue=null
>>>>>> GO
>>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'lazy
>>>>>> schema validation', @optvalue=N'false'
>>>>>> GO
>>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'query
>>>>>> timeout', @optvalue=N'0'
>>>>>> GO
>>>>>> EXEC master.dbo.sp_serveroption @server=N'serverB', @optname=N'use
>>>>>> remote collation', @optvalue=N'true'
>>>>>>
>>>>>>
>>>>>> "Russell Fields" wrote in message
>>>>>>
>>>>>>> Jim,
>>>>>>>
>>>>>>> Does it work and fail for the same person? Or for some persons it
>>>>>>> always works and for others it always fails? This would suggest a
>>>>>>> permissions issue on the server to which you are linking. If so,
>>>>>>> make sure that all users of the linked server have the needed
>>>>>>> permissions on that other server.
>>>>>>>
>>>>>>> If that is no help, could you script out your linked server
>>>>>>> definition and linked server login definitions? In SSMS right click
>>>>>>> on the linked server name and Script Linked Server as \ CREATE to
>>>>>>>
>>>>>>> Feel free to change names, but please do not hide anything else.
>>>>>>>
>>>>>>> RLF
>>>>>>>
>>>>>>>
>>>>>>> "Jim" wrote in message
>>>>>>>
>>>>>>>> Corrected error message:
>>>>>>>>
>>>>>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>>>>>> The SCHEMA LOCK permission was denied on the object 'table1',
>>>>>>>> database 'db1', schema 'dbo'.
>>>>>>>>
>>>>>>>>
>>>>>>>> "Jim" wrote in message
>>>>>>>>
>>>>>>>>> Good day!
>>>>>>>>>
>>>>>>>>> This much works fine:
>>>>>>>>>
>>>>>>>>> sp_addlinkedserver 'serverB'
>>>>>>>>> go
>>>>>>>>> create view [dbo].[serverB_db1_table1]
>>>>>>>>> as
>>>>>>>>> select * from [serverB].[db1].[dbo].[table1]
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> But querying the view
>>>>>>>>>
>>>>>>>>> select top 10 * from serverB_db1_table1
>>>>>>>>>
>>>>>>>>> might work fine, or it might return the error
>>>>>>>>>
>>>>>>>>> Msg 229, Level 14, State 71, Procedure sp_getschemalock, Line 1
>>>>>>>>>
>>>>>>>>> The SCHEMA LOCK permission was denied on the object
>>>>>>>>> 'blast_hsp_best', database 'vgd_blast', schema 'dbo'.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Any ideas out there?
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Thanks,
>>>>>>>>> Jim
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
>
 >> Stay informed about: View of Linked Server