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 |
|
modinrico
Starting Member
1 Post |
 Posted - 2007-03-16 : 17:32:58
|
| I have a quesiton that is hard for me but might be very easy for someone else...I am creating a "Reporting" server to allow my users access to but I don't want them directly accessing the Database. I have gotten this to work in Example A on the Same Server but i would prefer to have the reporting Server as a Completely seperate server thus using Linked Servers.Example A.Database A Has the tables being reported onDatbase B has a set of Views that point to Database A.DBO creates these views Thus the Views work for anyone having permission to the Views.User A only has Select permissions on those views and has a profile in both Database but has no permissions on Database A.The views work as expected happy happy joy joy.Example B.Database A Server A Has the tables being reported onDatbase B Server B has a set of Views that point to Database A.DBO creates these views Thus the Views work for DBO. I created a Linked Server as well as a Linked Login for SA.User A only has Select permissions on those views and has a profile in both Databases but has no permissions on Database A.I get an error when selecting from the views because User A has no permission in Database A.This is a Multi Tennant DB and the views on Database B will limit the user to the data they can see Based on their username.The goal is to let them select from the views but not be able to select from the other database because of their permissions.If you have any suggestions on how i can do this please let me know.RicoEdit/Delete Message |
|
|
rlaubert
Yak Posting Veteran
96 Posts |
Posted - 2007-03-21 : 08:11:22
|
| As I think you suspect, the problem is security. You will need to either create a linked account for the users in both databases or use an alias in Database A that users in Database B will be linked to. Using an alias doesnt sound like it would work for your situation because all users conntecting from B to A will use the same permissions.Now why does it work in example A and not B.... well in example A since the DBO is the owner of all objects permissions are checked only at the view. In example B there is a cross database ownership issue so SQL checks permissions on all objects. Hence User A now has to have access not only to the view on database B, but also the tables on Database A.Since your goal here is to limit access based on 'membership', why not create Windows Groups for each membership role. Grant that role select access to the tables on server A and select access on the views in database b. Pass through authenication should take care of the security.Raymond LaubertMCDBA, MCITP:Administration, MCT |
 |
|
|
|
|
|
Linked Server Question