Re: Viewing object owner in SQL 2005 - ownership chaining



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:
http://www.sommarskog.se/grantperm.html


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.



Relevant Pages

  • Re: Permissions and ODBC
    ... objects have the same owner. ... case of dbo-owned objects in different databases, ... See the SQL 2003 SP3 Books Online for more ... > The Ms Access program executes a stored procedure that resides in SQL ...
    (microsoft.public.sqlserver.server)
  • Re: object cant be accessed unless owner is specified - SQL 2000
    ... The only reasonable explanation is that at this point Sune was the ... the qualfied query with the owner ... thus his default schema was dbo. ... SQL 2000 certainly has schemas! ...
    (microsoft.public.sqlserver.security)
  • Re: Access 2007, SQL 2000
    ... Since users are not dbo, and SQL 2000 used the user name as the schema identifier, you have to make sure that every reference to a SQL Server object is fully qualified with the dbo. ... Normally a user should not be able to create any new objects in the db, but if they do, those new objects will be in the userName schema, not the dbo schema, assuming the user is not a db owner. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Stored Procedure Security Problem
    ... CREATE SCHEMA schema_1 AUTHORIZATION schema_1 ... This isn't valid SQL 2000 syntax, even though an error isn't raised. ... The specified owner must be an existing user and, as far as I know, is only used to validate that the specified user exists: ...
    (microsoft.public.sqlserver.security)
  • Re: freelists and dba_free_space
    ... Do you have to put place holders for the parameters that you don't want ... resides in SYS's schema. ... I tried granting EXECUTE ANY PROCEDURE to the schema owner and ... SQL> desc sys.dbms_stats ...
    (comp.databases.oracle.server)