Re: Ownership Chain Issue

From: Scott Shearer (ScottShearer_at_discussions.microsoft.com)
Date: 11/30/04 

Date: Mon, 29 Nov 2004 20:45:01 -0800

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:

> 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?
>
>
>

Relevant Pages

  • Re: Cant run views and stored procedures
    ... Do I need to manually set the permissions even if the view and stored proc ... Server, you must grant execute permissions on sProcs to the same user ... My account is in the VS Developers group. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Ownership Chain Issue
    ... I also see this behavior with a stored proc in an MS Access ADP. ... > unless the end user has appropriate permissions on the underlying table. ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: update multiple rows
    ... > for this you would need some sort of paging stored proc. ... > has to go to the server to send the info and to update the db, ... > 5) display page info to user. ... The end effort is to enable these 6 people to edit one ...
    (microsoft.public.inetserver.asp.general)
  • Re: schedule job - login in dynamic sql
    ... If the user created the stored proc without ... You have a userid as the login on the server. ... usually the Agent userid is a member of the sysadmin, or at least of dbo), ...
    (microsoft.public.sqlserver.programming)
  • Re: Sysadmin right for an application
    ... > The mssqlserverservice is set to run under a custom account i created with ... admin rights on the server. ... how are you running the proc on the prod server? ... Here is the command in my stored proc. ...
    (microsoft.public.sqlserver.security)