Re: Ownership Chain Issue
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 11/30/04
- Next message: Andrew J. Kelly: "Re: Moving a Database to another Server"
- Previous message: Felix: "how can i move a local directory with all files within a procedure"
- In reply to: Scott Shearer: "Re: Ownership Chain Issue"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 30 Nov 2004 08:00:12 -0600
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... >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? >> >> >>
- Next message: Andrew J. Kelly: "Re: Moving a Database to another Server"
- Previous message: Felix: "how can i move a local directory with all files within a procedure"
- In reply to: Scott Shearer: "Re: Ownership Chain Issue"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
