| Author |
Topic |
|
jgpdx
Starting Member
6 Posts |
 Posted - 2007-10-17 : 18:31:42
|
| I have two SQL servers setup; A - SQL 2005, and B - SQL 2000.I would like to create an account on Server A which has access to the results from one view via a linked server on Server B. I don't want the user on Server A to be able to access any databases, tables or even columns on Server B with the exception of those contained within this view.Is this possible, and how would I go about doing it? (Permission-wise) |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-17 : 18:39:46
|
| you can create the user on server A and give SELECT permission only for the view. Does that not work?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
jgpdx
Starting Member
6 Posts |
Posted - 2007-10-17 : 18:47:39
|
| I need to give UserA (on Server A) full sysadmin access and thus the view is located on Server B.When I attempt to do a select from the VIEW I get an "SELECT permission denied" error for the tables from which the view is SELECTING. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-18 : 00:08:58
|
| Would a Stored Procedure on Server B provide the necessary data? Then you can just provide EXECUTE permission on the Sproc.Kristen |
|
|
|
DMcCallie
Yak Posting Veteran
62 Posts |
Posted - 2007-10-18 : 10:43:01
|
| Create a SQL user on SystemB that only has select access to the View that you want. In the linked server setup on system A specify the remote login and password for the user on system B. Then test it with query analyzer to be sure that you have it setup correctly. Use the four part name or OpenQuery...DeWayne |
|
|
|
jgpdx
Starting Member
6 Posts |
Posted - 2007-10-18 : 11:28:04
|
| First off - thanks for all of the feedback, I appreciate it as I'm trying to get this accomplished with no luck thus far.I have setup a user B on Server B, with access to a view. I am connecting to Server B via a linked server, using the user B account. Thus I am performing a SELECT via the following:SELECT *FROM SERVERB.MyDatabase.dbo.V_MyViewMy problem however, is that I get a SELECT permission denied error for the tables which are being called from within the view. I get this even if I perform a SELECT from that view from within server B using user B.In short my question is this: Do I need to manually go through the columns in the tables on server B and grant SELECT permissions to user B or can I set it so that somehow I can grant inherited permission to any tables which that view is referencing? |
|
|
|
jgpdx
Starting Member
6 Posts |
Posted - 2007-10-18 : 15:24:42
|
quote:
Originally posted by Kristen
Would a Stored Procedure on Server B provide the necessary data? Then you can just provide EXECUTE permission on the Sproc.Kristen
I've also tried this, but without giving explicit SELECT permissions to various columns on the other table I get the following:"Server user 'userB' is not a valid user in database 'TestDatabase'." |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-18 : 23:03:02
|
| How did you map user a to remote user on linked server? Find a proper user on server b and map user a to it in linked server's security tab. |
|
|
|
jgpdx
Starting Member
6 Posts |
Posted - 2007-10-19 : 14:27:49
|
| Well FWIW I figured how to accomplish this.1) I had to check "Allow cross-database ownership chaining" under the SQL Server Properties (Security tab). This is by default in SQL 2000 unchecked, and thus disabled.2) I also had to make sure that the owners of the databases were setup the same way; that is the Login Name for dbo must be the same for each database in order for the permissions to chain. I had to update the sysusers table to set the Login Name to my 'sa' account.After I fixed these two items I was able to restrict the userB account to have only SELECT access to one view on server B. On server A I set the linked server to log in to server B as userB.Now I can SELECT from the view on serverB via the linked server and it will auto-inherit any select permissions on tables which are referenced in the view without allowing any other access on server B._Jordan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-20 : 02:49:51
|
| "This is by default in SQL 2000 unchecked, and thus disabled."That behaviour was introduced in a service pack for SQL 2000 - SP3 I think |
|
|
|
|
Possible between SQL2005 and SQL2000?