Insert Into on Linked Server

  • Hi

    I've got a linked server that was created with the SQL Server 2008 wizard (not T-SQL).

    It works fine for a user with full access (with dbo prefix), but if I use a user that only have write access to tables then it doesn't work. Any help please?

    The statement I run is:

    INSERT INTO [196.41.x.x].DBName..TableName ( Field1, Field2)

    VALUES ('xx', 'yy')

    The error I get is:

    Msg 7313, Level 16, State 1, Line 2

    An invalid schema or catalog was specified for the provider "SQLNCLI10" for linked server "196.41.x.x".

    If I use:

    INSERT INTO [196.41.x.x].DBName.dbo.TableName ( Field1, Field2)

    VALUES ('xx', 'yy')

    then it works for my admin user, but for the user with write access only I get the error:

    Cannot process the object ""DBName"."dbo"."TableName"". The OLE DB provider "SQLNCLI10" for linked server "196.41.x.x" indicates that either the object has no columns or the current user does not have permissions on that object.

  • I'm not sure but see if this can help u.

    http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx



    Pradeep Singh

  • Hi

    Thank you, but no - it's not helping.

    Firstly because it seems like a different error that is discussed and it is specific to Windows authentication. I connect via a SQL authentication.

    Thanks anyway.

  • I think you need the VIEW DEFINITION permission.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Is both the linked servers are SQL server 2008 or one is 2005 , other 2008 , then try recreating the linked server again using SQL server client and try runnning the queries .

    One more thing , Linked server is created using which security context . Either "Login's current security context " or "remote login " . If remote login no probs , whereas other case check whether the id exist in both server and it has necessary permission in destination server table not alone in source

  • Thanks a lot, but...

    The user have all permissions except dbo, but it gives the error:

    An invalid schema or catalog was specified for the provider "SQLNCLI10" for linked server "196.41.x.x.

    Sigh.

  • you stated in your original post that the user has only write access, that is why I stated it need VIEW DEFINITION.

    It works fine for a user with full access (with dbo prefix), but if I use a user that only have write access to tables then it doesn't work.

    Are you able to select from the linked server or do you get the same error? Which roles does the users have/Are the user defined or delivered?

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Hi

    Yes, sorry - just to clarify: in the orginal post the use only had write access, but after you post, it was changed to have all access, except dbo.

    And no, even if I try to read data it still fails:

    SELECT *

    FROM [196.41.x.x].database..table

    An invalid schema or catalog was specified for the provider "SQLNCLI10" for linked server "196.41.x.x".

    The users are user defined.

    Thanks for the replies.

  • rajganesh.dba asked if both servers were 2008 or if one was a earlier version. What is the answer to that?

    Also would you be able to post a script of you linked server(edit out any identifying information)?

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Oh yes.

    The linked server is 2005. I've tried with the connecting server on 2008 and 2005.

    The script for the linked server:

    /****** Object: LinkedServer [196.41.x.x] Script Date: 06/23/2009 15:40:11 ******/

    EXEC master.dbo.sp_addlinkedserver @server = N'196.41.x.x', @srvproduct=N'SQL Server'

    /* For security reasons the linked server remote logins password is changed with ######## */

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'196.41.x.x',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'196.41.x.x',@useself=N'False',@locallogin=N'sa',@rmtuser=N'myuser',@rmtpassword='mypassword'

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'collation compatible', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'data access', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'dist', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'pub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'rpc', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'rpc out', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'sub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'connect timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'collation name', @optvalue=null

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'lazy schema validation', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'query timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'use remote collation', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'196.41.x.x', @optname=N'remote proc transaction promotion', @optvalue=N'true'

    GO

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply