Linked server Problem

Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.|||Also make sure that SA is the owner in all of the DB's. I have seen issues like this when you have different users owning the different DB'
Jef
MCDBA, MCSE+I|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in
>message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
>> Hi,
>> We linked 2 sql servers and executing stored procedures
>> between the two servers.
>> We are using the 4 part key to reference a table.
>> We can able to execute sql statements with four part
key
>> from sqlserver1 to sqlserver2 for certain databases and
>> cannot able to do on certain databases. All settings
and
>> users are same in all databases.
>> For eg: My query is
>> 'select * from sqlserver1.db1.dbo.table1'.
>> For some database, I getting following error
>> Server: Msg 7314, Level 16, State 1, Line 1
>> OLE DB provider 'SQLSERVER1' does not contain
>> table '"db1"."dbo"."table1"'. The table either does
not
>> exist or the current user does not have permissions on
>> that table.
>> OLE DB error trace [Non-interface error: OLE DB
provider
>> does not contain the table: ProviderName='SQLSERVER1',
>> TableName='"db1"."dbo"."table1"'].
>> Any help would very much appreciated.
>> Thanks in advance.
>> Rajah V.
>
>.
>|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>

linked server msdb stored procedures

exec [ltd-gln-db2].msdb..sp_help_job
exec [ltd-gln-db2].msdb..sp_help_alert
Neither line fails.
The first returns 0 rows.
The second returns > 0 rows.
Both stored procedures allow 'public' to 'exec'.
Question: Why does sp_help_job return no rows?
Note: SQLServer 2000 SP2 on all db servers.Hi
Are there any jobs on the remote system?
As sp_help_job does not call sp_help_alert therefore I am not sure why you
should think sp_help_job would return anything if sp_help_alert does?
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
>|||
>--Original Message--
>Hi
>Are there any jobs on the remote system?
>As sp_help_job does not call sp_help_alert therefore I am
not sure why you
>should think sp_help_job would return anything if
sp_help_alert does?
>John
>"Larry Storm" <larry.storm@.ltd.org> wrote in message
>news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
>
>.
>When I am connected to the remote system, sp_help_job
displays info for multiple jobs.
The reason I included information for sp_help_alert was to
show the remote server is accessable as a linked server and
permissions on stored procedures allow sp_help_alert to
return results.
My question remains:
Why does sp_help_job return zero rows?|||Hi
Try profiling the procedure and then see what the steps do when you log in
locally as that user.
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:1a0001c5392e$f9f0e4c0$a401280a@.phx.gbl...
>
> not sure why you
> sp_help_alert does?
> displays info for multiple jobs.
> The reason I included information for sp_help_alert was to
> show the remote server is accessable as a linked server and
> permissions on stored procedures allow sp_help_alert to
> return results.
> My question remains:
> Why does sp_help_job return zero rows?
>
>|||Larry Storm wrote:
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
From BOL: "A user who is not a member of the symin fixed role can use sp_
help_job to view only the jobs he/she owns."
HTH,
Igor