August 24, 2009 at 10:43 am
Is this possible? Basically, I have some data in tables located on database A, and my application which exists in database B wants to access that data. So, I want to create a view in database B, which does some selects on the data located in database A.
What I want to avoid doing is having to grant access to the tables in database A. I'd rather be able to just grant access to the view in database B, so that the user doesn't have any permissions on database A.
Both databases are on the same server.
August 24, 2009 at 11:12 am
In a word: Yes.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2009 at 11:15 am
Bob Hovious (8/24/2009)
In a word: Yes.
Be sure to test. I vaguely remember running into this and going across databases I had to grant access to the tables in the other database. When done in the SAME database, then no you don't have to grant access to the underlying tables.
August 24, 2009 at 11:22 am
Alrighty then, Bob, how? 😛
My view in database B is running a query along the lines of,
SELECT FIELDS
FROM DatabaseB.dbo.Table1
JOIN DatabaseB.dbo.Table2 ON Table1.Column = Table2.Column
...etc...
I don't want to give the user access to tables 1 and 2. What I want is to give the user access to the view in database B. So, I go in and give the user SELECT permission on the view. But, when the user tries to run queries accessing the view, EG SELECT * FROM [View], a PERMISSION DENIED error is thrown, specifically, "The server principal [UserName] is not able to access the database [Database A] under the current security context."
August 24, 2009 at 11:25 am
Cross database queries, you will need to grant them access to underlying tables in the other database.
I don't think granting just public access to the other database will work. Pretty sure now based on your error message that you will have to grant select access to the tables.
August 24, 2009 at 12:50 pm
You could, although it pains me to suggest it, use DB Chaining, and as long as the tables (and I think databases) are owned by the same user you don't have to grant rights to the underlying tables..
CEWII
August 24, 2009 at 12:51 pm
That's unfortunate. There's no way you can have the user essentially "impersonate" a higher-access user when running that view only? I know with functions you can say "WITH EXECUTE AS ____"; that sort of functionality doesn't exist at the procedure or view level though?
August 24, 2009 at 12:52 pm
Elliott W (8/24/2009)
You could, although it pains me to suggest it, use DB Chaining, and as long as the tables (and I think databases) are owned by the same user you don't have to grant rights to the underlying tables..CEWII
This was one thing which one of our other DB people brought up when we were talking about it; she said though, that DB chaining was pretty much disabled in SQL Server 2005 and onwards, and that you had to do a bit of dancing in order to enable it, and that it was strongly discouraged from being done.
August 24, 2009 at 1:02 pm
I think it is pretty easy to enable, I just REALLY don't suggest it.. You might create views on those tables in the other DB and grant access to them, I'm not sure of the performance implications but it is a possibility..
CEWII
August 24, 2009 at 3:55 pm
We're on the same server right? I was thinking you'd just create the view in the SAME db as the tables, and grant access only to that view. (If the dbs were on separate servers, I would do that for performance reasons alone.) Why do you need the view to be in DB1 and the tables in DB2?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 9:34 am
Bob, wasn't that what I suggested?
CEWII
August 25, 2009 at 10:05 am
Having reread the last part of your last post: Yes, you did, Elliott. 🙂
I saw your opening line about not really suggesting it and thought your entire reference was to db chaining. My error. Performance difference should be negligible. It's all on the same server.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 10:10 am
Fair enough - thanks for the help, I'll go with the approach of accessing the other database for the view directly.
August 25, 2009 at 10:12 am
Since this is in a SQL Server 2005 forum something else to consider as well. Go ahead and create the views in the other database and grant access to the users to those views. The next step is a bit of smoke and mirrors, create a synonym for the each of the views in the main database so that it appears to be in the local database. Just a thought.
August 25, 2009 at 10:15 am
Actually Lynn, it is a little smoke and mirrors.. I'm working on an article related to Synonyms as border objects, which is what you are suggesting. However this article gave me some addition aspects to include..
CEWII
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply