Re: Viewing object owner in SQL 2005 - ownership chaining
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Thu, 13 Dec 2007 22:45:22 +0000 (UTC)
Wayne Erfling (wayne_erfling@xxxxxxxxxxxxxx) writes:
GlacierI'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?
By the default when you create an object, the owner of the object will be
the owner of the schema the object was created it. That is, if user
joe has permissions to create objects in the dbo schema, dbo will be
the owner those objects, not joe.
However, it is possible to transfer ownership of an object to someone
else than the schema owner.
2) How can I most easily LOOK AT the owner? The Properties for most
objects includes a "User" entry but not an "Owner" entry
The Properties page does not seem to show the owner.
You can use this query to determine the owner of an object:
select user_name(coalesce(o.principal_id, s.principal_id))
from sys.objects o
join sys.schemas s ON o.schema_id = s.schema_id
where o.name = 'tbl'
and s.name = 'schemaname'
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.
OK, here is the short story. In many databases, dbo owns everything.
But that does of course depend on how the objects were created.
For ownership to work across the databases, the databases must be owned
by the same login.
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.
I never remember if the login has to be a user in both databases, or
if the guest user comes in. In any case, if you have an EXECUTE AS
somewhere, this adds more complexity to the issue.
I have an article on my web site which to some extent discusses these
issue, and may sort some things out for you:
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at