Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Permission of linked Server

Author  Topic 

largpack
Starting Member

5 Posts

Posted - 2015-02-09 : 08:20:53
Hello everybody,

I have a question about permission of "Linked Servers". My aim is to move data from my MSSQL 2008R2 to an external MSSQL 2012 Server with a simple insert T-SQL. I have got a username and password for connection to the SQL Server 2012.

If I connect directly using SSMS I can see the database, the tables and thee structure of the table where I've permission. I can also write an insert SQL to insert some data.

Then I have created a linked server with the following settings:
> For a login nod defined in the list above, connections will:
>> Be made using this security context: username + password
> Provider Microsoft OLE DB Provider for SQL Server

I can expand the linked server node in SSMS on the left hand side, open the database and its tables, but: I cannot open at the table level to see the columns. I think this is also the reason, why I cannont insert data using an insert like "INSERT INTO [linked Server].[Database].[Schema].[Table] VALUES(....)". Is there some permission missing, or what can I check to get this working?

Hope someone can help me :)

Best Regards
Marcel


Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-02-09 : 11:23:40
If you log into the target server using the User/Password can you see the schema objects?



Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)
Go to Top of Page

largpack
Starting Member

5 Posts

Posted - 2015-02-10 : 02:03:59
Hello Bustaz,

yes if I don't use the linked server, then I can see the table and I am able to expand the table to see the columns and its structure. With the linked server this isn't possible. Is there any permission missing?
Go to Top of Page

largpack
Starting Member

5 Posts

Posted - 2015-02-13 : 03:43:20
any ideas?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-13 : 13:51:08
quote:

I think this is also the reason, why I cannont insert data using an insert like "INSERT INTO [linked Server].[Database].[Schema].[Table] VALUES(....)".



What error are you getting?

For the original question though, I don't think linked servers have the feature to navigate the structure like you can when connecting to the server. Just connect to the server instead to see the structure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

largpack
Starting Member

5 Posts

Posted - 2015-02-16 : 02:18:47
Thanks for the answers so far. I get the following Message, when I try to insert one record:

Cannot process the object ""TK_KPI_Database"."import"."AutomatedDataEntry"". The OLE DB provider "SQLNCLI10" for linked server "DRIVE_ENTW" indicates that either the object has no columns or the current user does not have permissions on that object.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 12:30:47
Is the linked server setup for Data Access? Check the Server Options page in the linked server properties.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

largpack
Starting Member

5 Posts

Posted - 2015-02-17 : 02:32:08
"Data Access" in the "Server Options" Page is set to true. I think this is the standard setting.
Go to Top of Page
   

- Advertisement -