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 |
|
svendoka
Starting Member
2 Posts |
 Posted - 2011-10-10 : 12:23:33
|
I have to create temporary tables based on data from 2 other servers and then compare them. I currently create these tables using the data import wizard.What I'd like to be able to do is create each table using a query like this:SELECT *INTO dbo.Results01_QAFROM LinkServer_QA.[db1].[dbo].[PNL] (nolock) where StartDate=[…] or for production:SELECT *INTO dbo.Results01_PRODFROM LinkServer_PROD.[db1].[dbo].[PNL] (nolock) where StartDate=[…] The thing I'm wondering about is, what are the consequences of setting up a linked server? Does it change how queries will run? For example, if I only have read privileges on a table on the prod DB server, would the table still be protected from inserts by me if I used a linked server in my SQL?Basically, I'm wondering whether I can argue that it has zero security implications for me to switch from the cumbersome data import process using the wizard to a SELECT INTO statement with the source server embedded in the query. I'm assuming what matters is my rights on the source server, not the method used.Thanks in advance.Thanks. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-10-10 : 12:31:00
|
| Depends how it's set up. If it uses the users login then it's whatever permissions that user has on the remote server.You might want to create a sql server login with read acces and use that for non-sa access then users won't be able to use the linked server to update and you won't have problems with permissions passing.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
svendoka
Starting Member
2 Posts |
Posted - 2011-10-10 : 13:10:32
|
Thanks, NIgel. So, you're saying that the linked server object inherits the privileges of its creator? The rights of the user writing the query are irrelevant?quote:
Originally posted by nigelrivett
Depends how it's set up. If it uses the users login then it's whatever permissions that user has on the remote server.You might want to create a sql server login with read acces and use that for non-sa access then users won't be able to use the linked server to update and you won't have problems with permissions passing.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-10-10 : 13:21:06
|
| Have a look at a linked server.It can use the current users security context (but that often has issues) or a specified sql server login.You can easily set one up and test it.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-16 : 05:10:20
|
| I would for a sql login on the destination server with privileges only having access to objects which you are going to query using linked queries.PBUH |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-10-16 : 09:19:28
|
| Instead of using a linked server, and worrying about the 'cumbersome' data import wizard - take a look at SSIS and using that.Once created, you can then schedule the SSIS package to run every day to repopulate your local tables without having to worry about setting up a linked server.Jeff |
|
|
|
|
|
|
security implications of linked server