CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
SQL Police
SQL Police

Reputation: 4206

Is the "owner" of a table/view/proc always a SCHEMA?

I did a lot of research on ownership chaining, but I am still not sure if I understood it.

So: Is it correct to say that the "owner" of a table/view/proc is always a SCHEMA ?

If A.PROC accesses B.TABLE, but the user has only execute right on A.PROC, then the procedure will fail. But if A.PROC accesses A.TABLE, then it will be executed ? Is that correct ?

Upvotes: 0

Views: 77

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

No. See Ownership and User-Schema separation:

By default, when developers create objects in a schema, the objects are owned by the security principal that owns the schema, not the developer.

...

A schema can also contain objects that are owned by different users and have more granular permissions than those assigned to the schema

And ALTER AUTHORIZATION:

Beginning with SQL Server 2005, a user can own an OBJECT or TYPE that is contained by a schema owned by another database user. This is a change of behavior from earlier versions of SQL Server.

So, if A.PROC and B.TABLE are both owned by D, then ownership chaining means that security checks on B.TABLE will not take place. Conversely, if A.PROC is owned by D and A.TABLE is owned by E then ownership chaining doesn't apply and a permissions check will occur.

Upvotes: 1

Related Questions