Do I need to grant permissions to table as well as view?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AkAlan - 13 Nov 2008 18:40 GMT

I have several views and I have granted update permissions to different users
against these views but when the user tries to update they get a permission
denied error referencing the table the view is against. When I grant update
permission to the table to the users can update. I don't need to grant
permissions to the underlying table when using stored procedures in the same
manner. So should I change my design to use stored procedures instead of
views if I don't want the hassle of assigning permissions to tables or am I
missing something?

Reply to this Message

Russell Fields - 14 Nov 2008 19:33 GMT

AkAlan,

Well, it works fine for me on SQL Server 2005 to GRANT UPDATE to a view, but
not to the underlying table. Are your views against tables in the same
database? Are you using 2005? Any other clues?

RLF

>I have several views and I have granted update permissions to different
>users
[quoted text clipped - 9 lines]
> I
> missing something?

Reply to this Message

Dan Guzman - 15 Nov 2008 22:08 GMT

Try creating the view WITH VIEW_METADATA. Some applications retrieve
metadata via the APIs and attempt to access to the underlying tables unless
the VIEW METADATA option is specified.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

>I have several views and I have granted update permissions to different
>users
[quoted text clipped - 9 lines]
> I
> missing something?

Reply to this Message

Kalen Delaney - 16 Nov 2008 02:31 GMT

Hi AkAlan

It depends on whether the view and the table are have the same owner (or are
in the same schema). If they have the same owner, then SQL Server will not
recheck permissions on the table if the right permissions are available on
the view. However, if the view and the table have different owners,
permissions has to be granted to the table separately.

You can read about this behavior in the article called "Ownership" chains in
the Books Online.

Signature

HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

>I have several views and I have granted update permissions to different
>users
[quoted text clipped - 9 lines]
> I
> missing something?

Reply to this Message