Re: Viewing object owner in SQL 2005 - ownership chaining


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: security for 100+ user and dbs without having to signin
    ... The owner of a DB can always give themselves permissions. ... by someone unaware of security, then likely they were logged in as ... > database remove all unnecessary permissions from ...
    (microsoft.public.access.security)
  • RE: Access 97 Security issue Cant make a MDE
    ... "If the database is secured using user-level security, ... logged in as the owner of the database, or at least a member of the Admins ... > the dayabase as a user AND can sign in simply as admin, ... > that the user account has Modify Design or Administer permissions for the ...
    (microsoft.public.access.security)
  • Re: Problem with database
    ... Perhaps running the security wizard. ... report that there is no permissions to read or write them. ... tables with the problem report the owner as unknown. ... For background info the database is normally Access97 but the user has ...
    (microsoft.public.access.security)
  • Re: Viewing object owner in SQL 2005 - ownership chaining
    ... Not only can different database principal names have the same SID, it's not unusual to have the same principal name with different SIDs. ... 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. ... 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. ... 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? ...
    (microsoft.public.sqlserver.security)
  • Re: permissions to import after a new workgroup file?
    ... To import as a new owner from a previous owner, ... for the objects in the original database. ... Normally permissions are assigned via Groups. ... You shouldn't be altering security measures with the "default workgroup". ...
    (microsoft.public.access.security)