What should you do to allow the members of the Role_Admin role to update the Accounts table with the leastadministrative effort?

You are the database administrator for a banking firm and maintain all the SQL Server 2008 databases of thefirm.
You create the Accounts table in the Sales schema of the database to store all information regarding thefirm’s customers.
To update the account details of a customer’s account, you instruct another databaseadministrator, Michelle, to create a stored procedure named Upd_account_details in the Sales schema in thedatabase. Michelle runs the following script to create the Upd_account_details procedure:
CREATE PROC Upd_account_details
@AccountName nvarchar(40)
@Address1 nvarchar(40)
@Address2 nvarchar(40)
@City nvarchar(25)
@State nvarchar(2)
@Phone nvarchar(20) AS
INSERT INTO Accounts VALUES
(@AccountName, @Address1, @Address2, @City, @State, @Phone) GO
ALTER AUTHORIZATION ON OBJECT::Upd_account_details TO Michelle; GO
You want to assign permissions on this procedure to the members of the Role_Admin role. You issue the following statement:
GRANT ALL ON OBJECT::Sales.Upd_account_details to Role_Admin
When John, a member of the Role_Admin role, attempts to update a record with the Upd_account_details stored procedure, he receives an error and is unable to update the record.
What should you do to allow the members of the Role_Admin role to update the Accounts table with the leastadministrative effort?

A.
Issue the following statement:
GRANT Update ON OBJECT::Sales.Upd_account_details to Role_Admin

B.
Issue the following statement:
GRANT Update, Delete ON OBJECT::Sales.Upd_account_details to Role_Admin

C.
Add the Role_Admin role to the db_datawriter role.

D.
Run the ALTER AUTHORIZATION statement to change the ownership of the Upd_account_details stored procedure.

Explanation:

You should run the ALTER AUTHORIZATION statement to change the ownership of the Upd_account_details stored procedure. John is unable to update information in the Accounts
table because the owner of the table andthe owner of the stored procedure are different. You created the table, but the stored procedure was created byMichelle. Michelle ran a script to create the stored procedure that ran the ALTER AUTHORIZATION statement onthe stored procedure. The ALTER AUTHORIZATION statement changes the ownership of the stored procedureto Michelle. In this scenario, the owner of stored procedure is not the same as the owner of the table, and theownership chain will break. A broken ownership chain will cause the permissions to be checked for each object. Ifthe ownership chain is not broken, permissions are checked only at the procedure level. John does not havepermissions on the table and therefore will not be able to update the table using the Upd_account_details storedprocedure. You should run the ALTER AUTHORIZATION statement to remove Michelle as owner of the storedprocedure. To change the owner of the stored procedure, you could also drop the object, and re-create it. Bydoing so, the owner of the stored procedure will default to the owner of the schema. You should not issue the GRANT Update ON OBJECT::Sales.Upd_account_details to Role_Admin statement because you cannot explicitly grant the update permission on a stored procedure. To execute a storedprocedure, you require the execute permission on the stored procedure. You should not issue the GRANT Update, Delete ON OBJECT::Sales. pd_account_details toRole_Admin statement because you cannot explicitly grant the update and delete permissions on a stored procedure. To execute a stored procedure, you require the execute permission on the stored procedure. You should not add the Role_Admin role to the db_datawriter fixed database role because this will give the Role_Admin role, the update, insert, and delete permissions on all the tables in the database. The role onlyneeds permission on the Accounts table.

Objective:
Managing SQL Server Security

Sub-Objective:
Manage users and database roles.

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 > Securables > Ownership Chains