• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

Publish SQLView only

Hi,

I have set up publishing between two of our SQL servers but would like to configure it so that only the results returned from a view is replicated to the other server (to not replicate the source tables for the view to work) - how can I go about doing this?

Thanks...
0
AlwaysAStudent
Asked:
AlwaysAStudent
  • 4
  • 3
  • 3
1 Solution
 
DavidMorrisonCommented:
Hi, if you google the term "permission chaining" in sql server this should give you your answer


Thanks
0
 
AlwaysAStudentAuthor Commented:
The destination SQL server is actually at a remote site and not managed by me, so I'd like to minimize the data sent across as well as send only the data they need.
0
 
DavidMorrisonCommented:
You can still do this with permissions. what I would do is create a new schema, create any views you wish to publish to the remote server in this new schema then only give the user you are auth'ing into your server permissions to this schema in the relevant DB. As long as you ensure you set the owner of the new schema to the owner of the schema the tables are parented by this will work (this is effectively what permission chaining is)
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Anthony PerkinsCommented:
>>so that only the results returned from a view is replicated to the other server (to not replicate the source tables for the view to work)<<
Conisdering that a VIEW is just a representation of the underlying table data, I am not sure how you think that is going work.

But perhaps I am not following you...
0
 
DavidMorrisonCommented:
acperkins, do you know what permission chaining is?

The idea is you give perms to the view, this allows the user to access the data without allowing access to the base tables themselves. That's basically what permission chaining is.

So how would that not work? you allow access to the data in the tables via the view without exposing the tables themselves, that's what the author has asked for unless I'm missing something?
0
 
Anthony PerkinsCommented:
My comment was addressed to the author, why don't we let them respond and clarify.
0
 
AlwaysAStudentAuthor Commented:
acperkins: I know that a view does not contain any data, I was wanting to only replicate the results of the view to the destination server, rather than replicate the view and the tables which the view requires.

DavidMorrison: Thanks for the tip on permissions chaining - so I could set up a database on a destination server with permission chaining and not allow users to the tables in the actual database (even if they're sysadmin)?
0
 
DavidMorrisonCommented:
Not really, what you'd want to do is restrict what you're outputting rather than restrict what they can see once you've out put it, that way regardless of a persons perms on the destination you have complete control on what they see.
I was meaning for you to set up the schema in your source database and create the views in there etc as described above. Then change the login that is connecting from destination to your source to be mapped to a user that only has perms on the new schema. Does that make sense?


0
 
Anthony PerkinsCommented:
>>I was wanting to only replicate the results of the view to the destination server, rather than replicate the view and the tables which the view requires.<<
When you say "replicate the results of the view" you are referring to replicating the data?
0
 
AlwaysAStudentAuthor Commented:
Thanks, I looked at SQL permission chaining but decided that it would be easier to have the user connect to our database, if I need to put it on their server then I'll use what you suggested.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now