MSSQL and ALTER AUTHORIZATION on table has no affect? I have a schema called [test] with an owner X.
I transferred an ownership of one table [test].[table] (ALTER AUTHORIZATION) to owner Y.
Now, if I understand correctly, even though the owner X owns the parent schema [test], he/she should have no access to [test].[table] since an owner of that table is Y, correct?
Or must I explicitly DENY permissions to X on that table?
I tested this and owner X still has an access to [test].[table].
And btw, X is not member of db_datareader or db_owner ;-)
So the question is, what is the purpose of ALTER AUTHORIZATION on an object/table if other users still have access to it (regardless if they are owners of parent schema)?
Best,
Mike
I think the following quote should explain what is going on
&quot;Ownership of schema-contained objects can be transferred to any database-level principal, but the schema owner always retains CONTROL permission on objects within the schema.&quot;
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189462(v=sql.105)