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.
| 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 ServerI 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 RegardsMarcel |
|
|
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) |
 |
|
|
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? |
|
|
|
largpack
Starting Member
5 Posts |
Posted - 2015-02-13 : 03:43:20
|
| any ideas? |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. |
|
|
|
|
|
|
|
|
Permission of linked Server