Ownership Chain Issue

Here is the situation:
I create a database - I am dbo. In this database I have 1 table and 1
updateable view. dbo shows as the owner of both the table and the view. I
create a new database user and do not add them to any database or server
roles. I grant the new user select, update, insert and delete permissions o
n
the view.
The user can view all data through the view, however, they cannot add or
update. When they attempt to add or update an error is generated indicating
that they do not have insert permissions on the table (for an add) or that
they do not have select and update persissions on the table (for an update).
My understanding is that since I have an unbroken ownership chain that SQL
Server should not even be checking the permissions on the table.
What am I missing?Some tools/APIs require that you create the view with the VIEW_METADATA
option. Otherwise, the base tables are accessed directly rather than via
the view. You can use a Profiler trace to see if this is the case.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
news:B12E2447-6AAD-4722-B53C-EC84517FD80E@.microsoft.com...
> Here is the situation:
> I create a database - I am dbo. In this database I have 1 table and 1
> updateable view. dbo shows as the owner of both the table and the view.
> I
> create a new database user and do not add them to any database or server
> roles. I grant the new user select, update, insert and delete permissions
> on
> the view.
> The user can view all data through the view, however, they cannot add or
> update. When they attempt to add or update an error is generated
> indicating
> that they do not have insert permissions on the table (for an add) or that
> they do not have select and update persissions on the table (for an
> update).
> My understanding is that since I have an unbroken ownership chain that SQL
> Server should not even be checking the permissions on the table.
> What am I missing?|||I have noticed this behavior using Enterprise Manager and Microsoft Access
ADP. I also see this behavior with a stored proc in an MS Access ADP.
While I can execute a stored proc that contains an insert or update statemen
t
in the Access ADP, it won't work when using the stored proc as a bound objec
t
unless the end user has appropriate permissions on the underlying table.
Is this behavior as designed?
I'll try creating the view with the VIEW_METADATA option and report back.
Thanks
"Dan Guzman" wrote:

> Some tools/APIs require that you create the view with the VIEW_METADATA
> option. Otherwise, the base tables are accessed directly rather than via
> the view. You can use a Profiler trace to see if this is the case.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
> news:B12E2447-6AAD-4722-B53C-EC84517FD80E@.microsoft.com...
>
>|||VIEW_METADATA is needed in views with Access ADPs so that Access doesn't
access the underlying tables directly. This is by design.
AFAIK, there shouldn't be a problem with stored procedures as long as there
is no dynamic SQL in the proc. Dynamic SQL always breaks the ownership
chain.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
news:AD3BD957-D452-4CE5-96F0-B311A797872C@.microsoft.com...[vbcol=seagreen]
>I have noticed this behavior using Enterprise Manager and Microsoft Access
> ADP. I also see this behavior with a stored proc in an MS Access ADP.
> While I can execute a stored proc that contains an insert or update
> statement
> in the Access ADP, it won't work when using the stored proc as a bound
> object
> unless the end user has appropriate permissions on the underlying table.
> Is this behavior as designed?
> I'll try creating the view with the VIEW_METADATA option and report back.
> Thanks
> "Dan Guzman" wrote:
>|||Including the VIEW_META argument in the view definition worked. However,
SQL Server does appear to be checking permissions on the stored proc. I hav
e
a simple select statement in the stored proc that should be updateable when
accessed through an ADP. I get error messages indicating that the
permissions are being checked on the underlying table. However, when I
create a view using a select * against the table with the VIEW_METADATA
argument and reference that view in the SP rather than the underlying table,
it works OK.
By way of background, I am doing this in preparation for a project where row
level security will be required. I will filter data in a view and access th
e
views through stored procs. The users will have no direct access to any dat
a
other than through a view (which filters data appropriately) or an SP that
refernces one of the views.
"Dan Guzman" wrote:

> VIEW_METADATA is needed in views with Access ADPs so that Access doesn't
> access the underlying tables directly. This is by design.
> AFAIK, there shouldn't be a problem with stored procedures as long as ther
e
> is no dynamic SQL in the proc. Dynamic SQL always breaks the ownership
> chain.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
> news:AD3BD957-D452-4CE5-96F0-B311A797872C@.microsoft.com...
>
>|||> I have a simple select statement in the stored proc that should
> be updateable when accessed through an ADP.
Your proc has only a SELECT statement and no INSERT/UPDATE/DELETE
statements. What is actually happening here is that Access is modifying the
base tables directly due to data binding. This necessitates that users have
permissions on the underlying table because the data modifications are not
done via a proc.
It seems views with VIEW_METADATA will allow you to use updatable bound
controls but this won't provide the security you want with stored procedure
resultsets. If you must use procedures for your SELECT, one option is to
create and execute INSERT/UPDATE/DELETE procs instead of relying on data
binding. Another approach is you use application roles from within you app
so that you can use data binding yet still prevent ad-hoc access outside
your application. See
<http://support.microsoft.com/defaul...kb;en-us;318816> for more
information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
news:CCA6F68B-E42D-45EB-85AB-598827765FCC@.microsoft.com...[vbcol=seagreen]
> Including the VIEW_META argument in the view definition worked. However,
> SQL Server does appear to be checking permissions on the stored proc. I
> have
> a simple select statement in the stored proc that should be updateable
> when
> accessed through an ADP. I get error messages indicating that the
> permissions are being checked on the underlying table. However, when I
> create a view using a select * against the table with the VIEW_METADATA
> argument and reference that view in the SP rather than the underlying
> table,
> it works OK.
> By way of background, I am doing this in preparation for a project where
> row
> level security will be required. I will filter data in a view and access
> the
> views through stored procs. The users will have no direct access to any
> data
> other than through a view (which filters data appropriately) or an SP that
> refernces one of the views.
>
> "Dan Guzman" wrote:
>|||When the stored proc that I access through the ADP does a select from a view
created with the view_metadata argument, permissions appear only to be
checked on the stored proc and the view - not on the underlying table. I am
able to dpdates/inserts/deletes.
VIEW_METADATA was the answer - thanks for the help.
"Dan Guzman" wrote:

> Your proc has only a SELECT statement and no INSERT/UPDATE/DELETE
> statements. What is actually happening here is that Access is modifying t
he
> base tables directly due to data binding. This necessitates that users ha
ve
> permissions on the underlying table because the data modifications are not
> done via a proc.
> It seems views with VIEW_METADATA will allow you to use updatable bound
> controls but this won't provide the security you want with stored procedur
e
> resultsets. If you must use procedures for your SELECT, one option is to
> create and execute INSERT/UPDATE/DELETE procs instead of relying on data
> binding. Another approach is you use application roles from within you ap
p
> so that you can use data binding yet still prevent ad-hoc access outside
> your application. See
> <http://support.microsoft.com/defaul...kb;en-us;318816> for more
> information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Scott Shearer" <ScottShearer@.discussions.microsoft.com> wrote in message
> news:CCA6F68B-E42D-45EB-85AB-598827765FCC@.microsoft.com...
>
>