Ownership chain question

HI,
MS SQL 7.0 on NT 4 (clustered)
Our developers use Infomaker to create many end-user's reports. They are
used to creating many Stored Proc and many "intermediate" tables instead of
creating "pure" Infomaker Reports. Report's components are often modified
directly on Production database during office hours by thoses developpers.
All of our end-user's reports are going to be rebuilt with Crystal Report.
Is it a good idea to ask our developpers to locate thoses end-user's reports
components into another databases instead of mixing DATA and REPORTS
component into the same Production Database ? (I know that we must take care
of Ownership chain)
Thank you
Danny"Danny Presse" <dpresse@.congresmtl.com> wrote in message
news:eA6M9CfXDHA.2476@.tk2msftngp13.phx.gbl...
> HI,
> MS SQL 7.0 on NT 4 (clustered)
> Our developers use Infomaker to create many end-user's reports. They are
> used to creating many Stored Proc and many "intermediate" tables instead
of
> creating "pure" Infomaker Reports. Report's components are often modified
> directly on Production database during office hours by thoses developpers.
> All of our end-user's reports are going to be rebuilt with Crystal Report.
> Is it a good idea to ask our developpers to locate thoses end-user's
reports
> components into another databases instead of mixing DATA and REPORTS
> component into the same Production Database ? (I know that we must take
care
> of Ownership chain)
Yes. Definitely.
Give them guest access only to the production database (you need this for
cross-database views to work).
Create views in their database that proxy over to the production database.
This keeps them from having to bind to the database name of the production
database.
David

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