January 4, 2007 at 5:01 pm
I have a remote SQL Server 2000 server that will pull data from our local SQL Server 2000 server's database "A". On the local server, I set up a database container "B" that holds only a set of views that reference tables in database "A". I set up the link from the remote server to the local server with a SQL login and password in the local DB server. I had to give the same login SELECT permissions on the tables in "A" so that the views in "B" could reference the tables. This means that, if someone knew the name of database "A", they could connect to it directly and read the data unfiltered by the views in "B". Is there a better way to set this up so that the remote user can only see the data through the views in database "B"? Thanks.
January 4, 2007 at 10:52 pm
No. You know how to set up a view locally where the user has permission to the view but not the table(s) right? Do that and then on the remote server, the views should select from the views on Server A.
January 5, 2007 at 12:55 pm
Robert: Thanks for the reply, but I don't see how to set up the view as you describe. Can you please fill me in? Thanks.
January 5, 2007 at 1:00 pm
Create the views locally on the server that has the tables. Grant Select rights on the views to the account. Deny all rights to the underlying tables to the accounts. Then on the other server, create views that simple select from the other views.
On Server A
Create View dbo.vwTable1 As
Select *
From dbo.Table1 with(nolock)
On Server B:
Create View dbo.vwTable1ServerA As
Select *
From ServerA.DatabaseA.dbo.vwTable1
January 5, 2007 at 3:55 pm
No worky. I'm sure I'm doing something wrong.
I have two databases on Server A. In DB X I have the views, they point to tables in DB Y. On Server A I have a SQL login L1. I give SELECT perms to L1 on the views in DB X. If I don't also give SELECT perms to the underlying tables in DB Y, I get
'Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object...'
January 5, 2007 at 3:59 pm
You're not listening. Create the views in the same database as the tables.
In case you mised it: SAME DATABASE AS THE TABLES
January 5, 2007 at 4:35 pm
My original, stated goal was to set up a database container "B" that holds only a set of views that reference tables in database "A". I'm trying to separate the views, which are for one purpose, from the tables, which are for a very different purpose. You're telling me to put the views in the same container as the tables. Yes, that works, I just tried it. But is there no way for the views to reside in a different database from the tables?
January 5, 2007 at 7:03 pm
You can have different views in the other database that select from the views you just created. This is what I stated yesterday.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply