Grant Select on a view not base table when base table is in a different database

Question

I have a view which is selecting rows from a table in a different database   I d like to grant select access to the view  but not direct access to the base table   The view has a where clause restricting the number of rows   Can I grant select to the view and not the base table  or do I need to switch to a stored procedure  I would rather not do it the latter way

User · Answer

You can grant permissions on a view and not the base table   This is one of the reasons people like using views     Have a look here  GRANT Object Permissions  Transact-SQL

User · Answer

I have had this problem   It appears that although permission to  View1  as part of schema  schema1  needs to be granted by the owner  dbo  if View1 uses dbo table1   Unless a schema gets used which is not part of dbo then this problem may not become apparent  and the regular solution of  Grant Select to user  would work

User · Answer

As you state in one of your comments that the table in question is in a different database  then ownership chaining applies  I suspect there is a break in the chain somewhere - check that link for full details

User · Answer

I tried this in one of my databases    To get it to work  the user had to be added to the database housing the actual data   No rights were needed  just access   Have you considered keeping the view in the database it references   Re usability and all if its benefits could follow

User · Answer

GRANT SELECT ON  viewname  TO  user    should do it

User · Answer

I had a similar issue where I was getting the same error message for a user  I feel that by sharing my mistake  I can clear up the issue  answer the question  and prevent others from making the same mistake  I wanted a user to have access to 4 particular views without having access to their underlying tables  or anything else in the DB for that matter   Initially I gave them the database role membership of  db denydatareader  thinking that this would prevent them from selecting anything from any table or view  which it did - as I thought   though I then granted  select  on these 4 views assuming that it would work as I intended - it did not   The correct way to do it is to simply not grant them the db datareader role and simply grant  select  on the items which you want the user to be able to access  The results of the above was that the user was able to access absolutely nothing outside these 4 views - the tables which these views area based on are also not available to this user

User · Answer

I also had this problem  I used information from link  mentioned above  and found quick solution  If you have different schema  lets say test  and create user utest  owner of schema test and among views in schema test you have view vTestView  based on tables from schema dbo  while selecting from it you ll get error mentioned above - no access to base objects  It was enough for me to execute statement  ALTER AUTHORIZATION ON test vTestView TO dbo   which means that I change an ownership of vTextView from schema it belongs to  test  to database user dbo  owner of schema dbo  After that without any other permissions required user utest will be able to access data from test vTestView

User · Answer

GRANT SELECT ON  viewname  TO  user    should do it

User · Answer

GRANT SELECT ON  viewname  TO  user    should do it

User · Answer

Just have a materialized view then you don t have to worry about all other factors  This is only going to work if space and refresh time is not a big deal   materialized views are pretty cool

User · Answer

GRANT SELECT ON  viewname  TO  user    should do it

User · Answer

I also had this problem  I used information from link  mentioned above  and found quick solution  If you have different schema  lets say test  and create user utest  owner of schema test and among views in schema test you have view vTestView  based on tables from schema dbo  while selecting from it you ll get error mentioned above - no access to base objects  It was enough for me to execute statement  ALTER AUTHORIZATION ON test vTestView TO dbo   which means that I change an ownership of vTextView from schema it belongs to  test  to database user dbo  owner of schema dbo  After that without any other permissions required user utest will be able to access data from test vTestView

User · Answer

As you state in one of your comments that the table in question is in a different database  then ownership chaining applies  I suspect there is a break in the chain somewhere - check that link for full details

User · Answer

You can grant permissions on a view and not the base table   This is one of the reasons people like using views     Have a look here  GRANT Object Permissions  Transact-SQL

User · Answer

The way I have done this is to give the user permission to the tables that I didn t want them to have access to  Then fine tune the select permission in SSMS by only allowing select permission to the columns that are in my view  This way  the select clause on the table is only limited to the columns that they see in the view anyways     Shaji

User · Answer

Create view Schema1 viewName1 as  select   from plaplapla    Schema1 has all the tables   Create view Schema2 viewName2 as  select   from schema1 viewName1    schema2 has no tables only views schema   In this case you can execute  select   from viewName2  in schema2   BUT    If you deleted viewNmae1 from Schema1   Then ViewName2 will not work     Amani El Gamal  aljamalaisj81 gmail com

User · Answer

I tried this in one of my databases    To get it to work  the user had to be added to the database housing the actual data   No rights were needed  just access   Have you considered keeping the view in the database it references   Re usability and all if its benefits could follow

User · Answer

I had a similar issue where I was getting the same error message for a user  I feel that by sharing my mistake  I can clear up the issue  answer the question  and prevent others from making the same mistake  I wanted a user to have access to 4 particular views without having access to their underlying tables  or anything else in the DB for that matter   Initially I gave them the database role membership of  db denydatareader  thinking that this would prevent them from selecting anything from any table or view  which it did - as I thought   though I then granted  select  on these 4 views assuming that it would work as I intended - it did not   The correct way to do it is to simply not grant them the db datareader role and simply grant  select  on the items which you want the user to be able to access  The results of the above was that the user was able to access absolutely nothing outside these 4 views - the tables which these views area based on are also not available to this user

User · Answer

As you state in one of your comments that the table in question is in a different database  then ownership chaining applies  I suspect there is a break in the chain somewhere - check that link for full details

User · Answer

As you state in one of your comments that the table in question is in a different database  then ownership chaining applies  I suspect there is a break in the chain somewhere - check that link for full details

User · Answer

You can grant permissions on a view and not the base table   This is one of the reasons people like using views     Have a look here  GRANT Object Permissions  Transact-SQL

User · Answer

Create view Schema1 viewName1 as  select   from plaplapla    Schema1 has all the tables   Create view Schema2 viewName2 as  select   from schema1 viewName1    schema2 has no tables only views schema   In this case you can execute  select   from viewName2  in schema2   BUT    If you deleted viewNmae1 from Schema1   Then ViewName2 will not work     Amani El Gamal  aljamalaisj81 gmail com

User · Answer

I tried this in one of my databases    To get it to work  the user had to be added to the database housing the actual data   No rights were needed  just access   Have you considered keeping the view in the database it references   Re usability and all if its benefits could follow

User · Answer

Just have a materialized view then you don t have to worry about all other factors  This is only going to work if space and refresh time is not a big deal   materialized views are pretty cool

User · Answer

I tried this in one of my databases    To get it to work  the user had to be added to the database housing the actual data   No rights were needed  just access   Have you considered keeping the view in the database it references   Re usability and all if its benefits could follow

User · Answer

I have had this problem   It appears that although permission to  View1  as part of schema  schema1  needs to be granted by the owner  dbo  if View1 uses dbo table1   Unless a schema gets used which is not part of dbo then this problem may not become apparent  and the regular solution of  Grant Select to user  would work

User · Answer

You can grant permissions on a view and not the base table   This is one of the reasons people like using views     Have a look here  GRANT Object Permissions  Transact-SQL

User · Answer

The way I have done this is to give the user permission to the tables that I didn t want them to have access to  Then fine tune the select permission in SSMS by only allowing select permission to the columns that are in my view  This way  the select clause on the table is only limited to the columns that they see in the view anyways     Shaji

[sql-server] Grant Select on a view not base table when base table is in a different database

Examples related to sql-server