Re: Viewing object owner in SQL 2005 - ownership chaining

Erland brought up a good point that chaining is ultimately based on SID rather than name. Not only can different database principal names have the same SID (different user names, same login), it's not unusual to have the same principal name with different SIDs (same user name, different logins). A common example of the latter is when the database owners are different ('dbo' user maps to different logins), which breaks the cross-database ownership chain for dbo-owned objects. This is confusing at first glance because all objects/schema are owned by 'dbo' but it is really the login SID that is used for chaining.

If I get this sorted out into a set of steps minus fluff, any suggestions on where I might post a "How To" on Ownership Chaining?

There are SQL Server community sites that accept articles like this. Google "SQL Server community articles".

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Wayne Erfling" <wayne_erfling@xxxxxxxxxxxxxx> wrote in message news:eP6P9TmPIHA.4440@xxxxxxxxxxxxxxxxxxxxxxx
This helps a lot. I hadn't mentioned that I also had come across a mention of the login being tangled into ownership.

One question in particular remains in my mind:
I see that the owner is not the schema itself, so it must be a USER entity, or a LOGIN entity, or some dynamic combination of them (e.g. join the user to the current login and make the comparision based on the login).

Which type of entity / expression is the owner? Or can it be sometimes one and sometimes another?
If I get this sorted out into a set of steps minus fluff, any suggestions on where I might post a "How To" on Ownership Chaining?

Thanks again!

---Wayne
"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:26A475ED-7BAA-4494-ABDE-704F6BC848D4@xxxxxxxxxxxxxxxx

Msg 916, Level 14, State 1, Line 1
The server principal "HFDB_2_0_readonly" is not able to access the
database "Hotfix_data" under the current security context.
which I believe means that Ownership Chaining failed to "kick in", so that
the SELECT looked at the permissions on the target table.

To add on to Erland's response, the user needs a security context in both
databases, even if no permissions are granted. You either need to add the
user to the other database or enable the guest user in that database.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Wayne Erfling" <wayne_erfling@xxxxxxxxxxxxxx> wrote in message
news:%23B1bryXPIHA.292@xxxxxxxxxxxxxxxxxxxxxxx
I'm trying to set up Ownership Chaining (though I would have preferred it if
SQL 2005 supported EXECUTE AS for views).

When I set up both a table and view from one logon (mine) and granted
permissions to the view it didn't work, evidently because "creating user" is
not equivalent to OWNER. I understand (including the potential risks) and
enabled Cross Database Ownership Chaining.

I'm beginning to understand that OWNER lives somewhere between the schema
and the USER, but I'm having trouble finding two things:

1) What entity (and what type of entity) is the OWNER of an object such as a
VIEW, to which Ownership Chaining applies?

(i.e. the precise definition - is the schema the owner or is a USER the
owner, or is the login associated with a USER the owner, etc.)

2) How can I most easily LOOK AT the owner? The Properties for most objects
includes a "User" entry but not an "Owner" entry




Unfortunately (before you tell me to look at the page!) the Ownership
Chaining example in the books online introduces a bit too much "fluff" in
the way of setting up users with default schemas and such, and for me this
obscures the definition of who / what the OWNER is.

The basic idea of Ownership Chaining is clear to me, but Owner is simply not
defined in the overview, the example or the Books Online Glossary (or on the
Internet, so far I've only found references to how owner is used, not the
definition).

Many thanks in advance.

---Wayne

P.S. The error message that my "read permission" user gets is:

Msg 916, Level 14, State 1, Line 1
The server principal "HFDB_2_0_readonly" is not able to access the database
"Hotfix_data" under the current security context.
which I believe means that Ownership Chaining failed to "kick in", so that
the SELECT looked at the permissions on the target table.

I'm sure I can resolve this easily once I understand who the owner is of the
VIEW and TABLE so that I can make them match (since it is clearly not the
creating login).

.

Relevant Pages

  • Re: Viewing object owner in SQL 2005 - ownership chaining
    ... The server principal "HFDB_2_0_readonly" is not able to access the database "Hotfix_data" under the current security context. ... To add on to Erland's response, the user needs a security context in both databases, even if no permissions are granted. ... I'm trying to set up Ownership Chaining. ... When I set up both a table and view from one logon and granted permissions to the view it didn't work, evidently because "creating user" is not equivalent to OWNER. ...
    (microsoft.public.sqlserver.security)
  • Re: Cross DB ownership chaining problem
    ... is that the owner of database D is different than the other databases. ... you don't need to turn on cross-database chaining at the server ... level (sp_configure 'Cross DB Ownership Chaining') unless you want the ...
    (microsoft.public.sqlserver.security)
  • Re: Permissions on sp_OASetProperty
    ... this be setup with the least amount of permissions being given. ... chaining to allow limited access to the sp_OA* procs and other potentially ... dbo-owned objects in that database. ... EXEC sp_dboption 'MyDatabase', 'db chaining', true ...
    (microsoft.public.sqlserver.security)
  • Re: Using Views for Security
    ... A security consideration when cross-database chaining enabled is that you ... must fully trust users that have permissions to create dbo-owned objects. ... in order to access objects in a database. ... It is on cross-database ownership. ...
    (microsoft.public.sqlserver.security)
  • Re: Security - Stored Procedures
    ... This is known as ownership chaining. ... Permissions on indirectly referenced objects are not required. ... owned by the same login since database ownership determines the user/login ...
    (microsoft.public.sqlserver.security)