Grant Views to User

Hi all,

I have a question on the grant issue. I have created a view base on three tables, and I grant 'select' to the user to see the views I have created. But when the user select this views, it say :
"SELECT permission denied on object 'Table1', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table2', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table3', database 'XX', owner 'XX'."

I grant this three table to the user, and it run successfully.

Question is whether is there a way I can let user run the views but without seeing the three tables? or is there a way to hide from user?

Thanks in advance.

Are the tables in another database?

If the tables are in another database than the user, see if this applies (from BOL)

Cross-database permissions are not allowed; permissions can be granted only to users in the current database for objects and statements in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database.

/Kenneth

|||

Hi Kenneth,

The views and tables are in the same database. But even in same database it give me the same problem that I have mention in my first post on this topic.

Thanks.

|||Are the views and the table owned by different users ? If they are owned by different users then you should see this error, otherwise it will be a security violation.|||

Hi,

Ya, the tables is created using one user account and the views is created by another user account.

So you mean that I need to create the views using the user account which create the tables? Or is there any other way to solve this?

Thanks.

|||

The 'old way' to avoid this kind of problems (broken ownership chains), is to have all objects in the database
being owned by the same user - eg 'dbo'

The easy way to accomplish that, is to always create objects with two-part names (dbo.myNewtable, dbo.myProc etc..)

/Kenneth