Re: SQL 2k5 Views Referencing Different Schemas - Permissions Issues

Erland,

You are exactly right. Insuring all the schemas had the same owner
fixed the issue. This does make me wonder why the stored procedures
didn't have the permissions problems, but the veiws did.

Admittedly, I'm still trying to figure out how maximize schema use and
need to learn more.

The supplied link didn't seem to have any information pertaining to
schema/ownership chains.

Many thanks,

Greg

On Tue, 3 Apr 2007 22:20:55 +0000 (UTC), Erland Sommarskog
<esquel@xxxxxxxxxxxxx> wrote:

Greg Wilkerson (gregwilkerson@xxxxxxxxxxx) writes:
I have a view in one schema that references tables in a different
schema.

When the user attempts to use this view, a "select permission denied"
is returned for the tables outside of
the schema the view is defined in.

It sounds to me as if the schemas have different owners. In this case the
ownership chaining is broken.

If there any particular reason that the schemas have different owners?
Else it would be simpler to have the same owner for all.

For more inforamtion about ownership chaining, you may be interested in
the first part of an article on my web site:
http://www.sommarskog.se/share_data.html.
.