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.
This question is related to
sql-server
The answer is
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.
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.
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)
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 [email protected]
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.
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.
GRANT SELECT ON [viewname] TO [user]
should do it.
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.
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.
GRANT SELECT ON [viewname] TO [user]
should do it.
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.
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
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.
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.
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)
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
GRANT SELECT ON [viewname] TO [user]
should do it.
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
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)
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 [email protected]
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
Source: Stackoverflow.com