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

I implemented a similar scenario to what you described, with the result that
both the schema and the procedure exhibited the same behavior. The only
reason why your experience may be different is if you set an explicit owner
on the procedure - the owner of the schema where the table resided - then
you would have ownership chaining as well.

You can check sys.objects for the principal_id value of the row
corresponding to your procedure. I expect the value is not NULL and it is
the ID of the owner of the schema containing the table. The row
corresponding to your view probably had a NULL value, meaning it was owned
by the schema owner.

With the introduction of schemas and for backward compatibility reasons,
objects created in a schema are owned by the schema owner (principal_id is
NULL), but the ownership can be changed explicitly, in which case the
principal_id column will point to the object owner. Ownership of objects is
changed using the ALTER AUTHORIZATION statement.

Thanks

--
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/

This posting is provided "AS IS" with no warranties, and confers no rights.

"Greg Wilkerson" <gregwilkerson@xxxxxxxxxxx> wrote in message
news:uu9713d7fogm7vk3j6uu5rpfjikpovhsk0@xxxxxxxxxx
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.


.

Relevant Pages

  • Re: created by attribute
    ... track this separarately rather than at the security descriptor level. ... the owner is stored in the ACL of the object... ... I would not extend the schema for that. ... portal the portal admins can create new users. ...
    (microsoft.public.windows.server.active_directory)
  • Re: Can a DBA restrict privileges of a user that is the owner of a schema? We need to remove DDL pri
    ... User STEPH is the owner of schema WORK. ... restrict STEPH to have just DML on WORK schema. ...
    (comp.databases.oracle.server)
  • Re: SQL Connection String
    ... I want to access a database which has a different owner. ... If this is SQL Server 2000, then I can guarantee you mean owner. ... Server 2005 or greater, you probably mean schema. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: using EXECUTE AS for stored procedures, sql 2005
    ... Objects created within a schema are owned by the owner of the schema, and have a NULL principal_id in sys.objects. ... But if you are wanting to change execution contexts or your stored procedures you should really read Erland Sommarskog's article, ... Does that mean the stored procedure will be executed as the owner of the stored procedure? ...
    (microsoft.public.sqlserver.programming)
  • Re: Qualifier und Owner ermitteln
    ... und ein Owner stehen. ... Hättest Du im gestrigen Threads die Links aufmerksam gelesen, ... Soweit es den SQL Server betrifft: ... "Trennung von Benutzer und Schema" ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)