You are responsible for managing an instance of SQL Server 2008 named SQL1 . SQL1 contains a schemanamed HumanResource.
You want to transfer the ownership of the schema to a user named HRHead . Which Transact-SQL statement should you use?

A.
ALTER SCHEMA

B.
OBJECTPROPERTY

C.
OBJECTPROPERTYEX

D.
ALTER AUTHORIZATION

Explanation:

You should use the ALTER AUTHORIZATION Transact-SQL statement. A schema is a container of objects thatexists as a distinct namespace independently of the database user in SQL Server 2008. The ownership of a schema is transferable, and you can configure any user as the owner of the schema using the ALTERAUTHORIZATION Transact-SQL statement. This statement allows you to change the ownership of any entity thathas an owner. The ALTER AUTHORIZATION statement requires the following syntax: ALTER AUTHORIZATION
ON [ <entity_type> :: ] entity_name TO { SCHEMA OWNER | principal_name }; In this scenario, you want to transfer the ownership of the HumanResource schema to the HRHead
user. Toachieve this, you can use the following Transact-SQL statement: ALTER AUTHORIZATION ON SCHEMA::HumanResource TO HRHead; You should not use the ALTER SCHEMA Transact-SQL statement because this statement does not allow you tochange the ownership of a schema. The ALTER SCHEMA Transact-SQL statement allows you to transfer asecurable from one schema to another. You should not use the OBJECTPROPERTY or OBJECTPROPERTYEX Transact-SQL statement becauseneither of these statementsallows you to change the ownership of a schema. These statements only provideinformation about schema-scoped objects.

Objective:
Managing SQL Server Security

Sub-Objective:
Manage schema permissions and object permissions.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > ALTER AUTHORIZATION (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Security and Protection > Identity andAccess Control > Principals > User-Schema Separation