October 20, 2009 at 2:41 pm
I'm using MS SQL 2008 and I've created views that restrict the rows that a user can see. I've given the user the ability to select out of the views but not the tables. I cannot give them select access to the tables because then the user could see all of the data in the table and I don't want that. When the user tries to query the view they get the message "The SELECT permission was denied on the object 'table_name', database 'database_name', schema 'db_owner'".
How can I setup the database and/or user so that they have access to the tables through the view but not direct access to the tables?
Thanks in advance,
Adam
October 20, 2009 at 3:51 pm
Are those tables in a different database? If so, then you would have to create the user in the other database and give them access in that other database also.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 20, 2009 at 5:58 pm
Just grant SELECT permissions on the view and don't grant SELECT permissions on the underlying tables. This will allow the user to query the view but not the tables.
If you just need to keep the user from querying a particular set of tables, you can grant them the db_datareader role and then explicitly deny them SELECT permissions on the tables.
If you want the user to be able to query the view and only certain columns in the underlying tables you can explicitly deny SELECT permissions on the columns in the table you don't want them to see.
October 21, 2009 at 6:42 am
I specifically didn't give them db_datareader because I don't want to have to maintain the user/table when tables are added to the database.
@ Jeffrey: The tables are in the same database as the views.
@ Edogg: I tried what you said before I posted (SELECT on the views but not the tables - sorry I didn't mention it). This works in SQL 2005 but not in SQL 2008 for some reason. I thought that if the user had SELECT access to the view then they didn't need SELECT access to the tables that the view accesses. That doesn't seem to be the case with 2008.
October 21, 2009 at 7:13 am
just made this test and worked 100%....
create login test with password = '123QWEasd'
go
create user test for login test
go
create table t1 (id int, col0 nvarchar(10))
go
create table t2 (id int, idt1 int, col1 nvarchar(10))
go
create view vt1t2 as select t2.id, t2.idt1, t1.col0, t2.col1 from t2 inner join t1 on t2.idt1 = t1.id
go
grant select on vt1t2 to test
go
then I connected to the database using the test login and ran
select * from t1
go
select * from t2
go
select * from vt1t2
go
and the output was:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 't1', database 'DMDW', schema 'dbo'.
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 't2', database 'DMDW', schema 'dbo'.
(0 row(s) affected)
Pedro
October 21, 2009 at 9:42 am
@PiMané: I tried that and it works for me too.
I looked at the tables that the code above created and I noticed that the tables were created with a "dbo." prefix. The tables that I was working with are prefixed with a "db_owner." prefix. I recreated the tables with a "dbo." prefix and now everything is working.
Does anyone know why it works with the dbo. and not the db_owner.? Are users automatically added to the dbo. schema and not the db_owner. schema or something? I just want to know so I know going forward.
Thanks to everyone who responded!
October 21, 2009 at 10:01 am
Hi,
I create the dbo_owner schema and did the script on that schema
create schema dbo_owner
go
create table dbo_owner.t1 (id int, col0 nvarchar(10))
go
create table dbo_owner.t2 (id int, idt1 int, col1 nvarchar(10))
go
create view vt1t2 as select t2.id, t2.idt1, t1.col0, t2.col1 from dbo_owner.t2 t2 inner join dbo_owner.t1 t1 on t2.idt1 = t1.id
go
create view dbo_owner.vt1t2 as select t2.id, t2.idt1, t1.col0, t2.col1 from dbo_owner.t2 t2 inner join dbo_owner.t1 t1 on t2.idt1 = t1.id
go
grant select on vt1t2 to test
go
grant select on dbo_owner.vt1t2 to test
go
Than I made the select on both views and they worked fine.
do you, by any chance, have a view with the same name but on the default schema and forgot to add the schema name to the view?! (probably not but just asking, since every thing seems to work fine).
October 21, 2009 at 10:44 am
The views were dbo. but the tables were db_owner. No I didn't have any views or tables with duplicate names.
October 21, 2009 at 11:11 am
that's weird... was that an upgrade from an instance of a previous sql server version?
Pedro
October 21, 2009 at 11:28 am
PiMané (10/21/2009)
that's weird... was that an upgrade from an instance of a previous sql server version?Pedro
That would definitely cause the problem you were seeing. Each schema would have had a different owner, breaking the ownership chain and causing the permissions issues you were seeing.
Just remember, now that we have schemas - the ownership chain is based upon the owner of the schema and not the schema name. So, you can have MySchema owned by 'dbo' - dbo schema owned by 'dbo' and MyOtherSchema owned by 'adam'. Objects in MyOtherSchema that reference objects in the other schema will not work if the user does not have access to the other schema.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy