October 13, 2009 at 1:54 pm
I recently added a new database to a server that was already part of a linked server. The problem is that the database is not showing as part of the catlog for that server. I am receiving the below error. How do I add an additional database to an already existing linked server.
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "KOPSQL02" does not contain the table ""Archive"."dbo"."MPI_logevent"". The table either does not exist or the current user does not have permissions on that table.
I definitley have permissions and i am the one running the stored proc.
Any help would be appreciated
October 13, 2009 at 3:03 pm
You are making an assumption about rights that is problematic. You have rights on the LOCAL machine, does your user (or the one used to make the connection to the other server) have rights in the new database. You have to satisfy security on THAT side too.. Based on the error you probably don't.
CEWII
October 14, 2009 at 5:12 am
I have full administrative rights on the Linked server as well. And I am the one running the query. Am I missing something in what other user needs permission?
October 14, 2009 at 7:10 am
When you created the linked server what login method did you define. If you are using a specific login does that have the correct rights ?
And the obvious - is all your spelling correct in your reference !
October 14, 2009 at 7:14 am
Here is the script I used for the Linked Server. The linked server has been there for a long time. It is just this database that is not available. If I look under the linked server at the catalog the new database does not show. Thanks for your help
EXEC master.dbo.sp_addlinkedserver @server = N'KOPSQL02', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'rpc', @optvalue=N'True'
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'dpub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'KOPSQL02', @optname=N'use remote collation', @optvalue=N'true'
October 14, 2009 at 7:18 am
Have you double checked on the second server to ensure there are no issues with the database. Offline, corrupt etc ?
October 14, 2009 at 7:20 am
yes. I am able to run queries on it. I can run the query in the opposite direction. Meaning I can go to the opposite server from the new database.
October 14, 2009 at 7:42 am
On the security tab of the linked sever, how do you have the security configured?
What radio button is selected?
CEWII
October 14, 2009 at 7:45 am
For a login not defined in the list above, connections will:
Be Made using the login's current security context
October 14, 2009 at 8:49 am
Both machines in the same domain?
You are logging in to the initiating machine as a windows user?
Just to see if it works at all why don't you key in a username and password and tell it to use that for everything. You might have to restart the SQL Server, I've seen weird behavior when that is changed after it has already tried other rights..
CEWII
October 14, 2009 at 10:29 am
They are in the same domain. And I am logging in as a windows user. I will have to wait to try putting in as a user until I can restart services. It's my production server so I can't take it down in the middle of the day. Thanks and i will let you know how it works out.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply