Grant Select to a view , but not to the others tables in the database.
Hi,
I need to grant the 'SELECT' privileges to a user for a View that belongs to another database, but i do not want to grant 'SELECT' to any table of that database to the user, or even any of the tables that belongs to that view. What happens is that I only want that user to be able to 'SELECT' to that view, for specific fields that belongs to that view.
For example.., if the views is built using TABLE A that has 8 fields, but in the view the display is only viewing 3 fields of that table, the user must be able to SELECT the view for only to see the fields that belong to the view, but can not see the others fields of the table, or any other table of the database.
Can i do it in MS SQL Server ?
Regards,
I need to grant the 'SELECT' privileges to a user for a View that belongs to another database, but i do not want to grant 'SELECT' to any table of that database to the user, or even any of the tables that belongs to that view. What happens is that I only want that user to be able to 'SELECT' to that view, for specific fields that belongs to that view.
For example.., if the views is built using TABLE A that has 8 fields, but in the view the display is only viewing 3 fields of that table, the user must be able to SELECT the view for only to see the fields that belong to the view, but can not see the others fields of the table, or any other table of the database.
Can i do it in MS SQL Server ?
Regards,
ASKER
If i do it like that the user can not ' SELECT ' the view. I need to grant the role 'db_datareader' for it to work. And doing that the user can select any table from the Database.
Did you grant the select on the view?
GRANT SELECT ON ViewName TO UserNameASKER
Yes,
I was able to do it.
I made the below steps.
a) Granted SELECT for that View to the user.
b) Granted SELECT on each tables that are part of the view.
c) Granted SELECT for each column of the tables that i want the user to be able to have access.
d) No role granted to user.
Like that the user can SELECT the view and if the user try to SELECT any other columns part of the table it won't be able.
Thanks.
I was able to do it.
I made the below steps.
a) Granted SELECT for that View to the user.
b) Granted SELECT on each tables that are part of the view.
c) Granted SELECT for each column of the tables that i want the user to be able to have access.
d) No role granted to user.
Like that the user can SELECT the view and if the user try to SELECT any other columns part of the table it won't be able.
Thanks.
I don't think you'll need the steps b) and c).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't understand the selected comment. I mean, it's right but nothing that I didn't say before.
I've wrote about this and you can read the article published here in EE.