March 4, 2008 at 9:47 am
We would like to create a view for a specific user login but prevent them from having permissions on the base table. What we have set up currently is not working and is described generically as follows:
dbo.tbl1
dbo.tbl2
otherschema.view1 defined as a select on certain fields in dbo.tbl1 and dbo.tbl2.
the user is the owner of otherschema...but unless we grant select permissions on the 2 base tables owned by dbo....it does not work.
I know we are missing something very basic...but I thought there was a way to give select on the view without giving full access to the underlying base tables.
March 4, 2008 at 11:00 am
Create a view;
Grant SELECT on this view to the user (GRANT SELECT ON VIEW1 TO viewtest);
The user does not have access to the related tables.
March 4, 2008 at 11:20 am
Thanks for the response...this is what we started with and it did not work as we expected. We granted select permissions to the view object...and when the user logs in and tries to use the view...it fails. The only way we were able to get it to work was to also grant select permissions to the base tables. The user is the owner of the schema that owns the view. Could it be this relationship that is causing the issue?
March 4, 2008 at 11:50 am
You are right. If the user is the owner of the view, it means that he has the same authority as the person who created this view. To create a view, a user should have necessary access to its base tables.
March 4, 2008 at 12:28 pm
Okay...so all we need to do is not make this user the schema owner...but rather just give them select permissions to the view...regardless of schema?
March 4, 2008 at 12:30 pm
to continue...so the correct solution would be:
assuming dbo.tbl1 and dbo.tbl2 exist
otherschema.view that selects on dbo.tbl2, dbo.tbl2
usertst whose default schema is otherschema...but he no longer owns the schema would simply be granted select permissions on otherschema.view
Is this accurate?
March 4, 2008 at 12:40 pm
Don't swap schema.
Just create a
create view dbo.myview
as
select a.col1, b.col1, ...
from dbo.table1 a
inner join dbo.table2 b
on a.key = b.fk4a
- if you add with check it checks the results of an update still
match the views definition.
WITH CHECK OPTION
go
grant select, update on dbo.myview to theotheruser.
go
Should do the job.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 4, 2008 at 1:35 pm
Thanks everyone.....when we removed the user as owner of the schema...and ljust made sure this was his default schema and he had select permissions on the view...it worked as expected!!
Where we goofed was making him the owner of the schema...which owned the view.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply