Which Transact-SQL script should you use?

You manage an instance of SQL Server 2008 named SQL1 . SQL1 contains a stored procedure named Sales_SP.
The Sales_SP is stored in a database named SalesDB in the Sales schema.
You discover that a temporary employee named Mary is able to run Sales_SP. You want to prevent Mary fromrunning the Sales_SP stored procedure.
You also want to prevent Mary from inheriting permission to run thestored procedure through any group or role membership.
Which Transact-SQL script should you use?

A.
USE SalesDB;
REVOKE EXECUTE ON OBJECT::SalesDB.Sales_SP TO Mary;
GO

B.
USE SalesDB;
REVOKE EXECUTE ON OBJECT::Sales_SP TO Mary;
GO

C.
USE SalesDB;
DENY EXECUTE ON OBJECT::SalesDB.Sales_SP TO Mary;
GO

D.
USE SalesDB;
DENY EXECUTE ON OBJECT::Sales.Sales_SP TO Mary;
GO

Explanation:

You should run the following Transact-SQL script: USE SalesDB; DENY EXECUTE ON OBJECT::Sales.Sales_SP TO Mary; GO Stored procedures are members of the OBJECT class. The DENY Transact-SQL statement is used to denypermissions on members of the OBJECT class. The DENY statement prevents a principal from inheritingpermissions through group or role membership. In this scenario, you want to prevent Mary from running thestored procedure. To achieve this, you should deny the EXECUTE permission to Mary on the stored procedure. You should not run the following Transact-SQL script: USE SalesDB; REVOKE EXECUTE ON OBJECT::SalesDB.Sales_SP TO Mary; GO The REVOKE Transact-SQL statement is used to remove a previously assigned granted or denied permission.The REVOKE Transact-SQL statement does not prevent a principal from inheriting permissions through group orrole membership. You should not run the following Transact-SQL script: USE SalesDB; REVOKE EXECUTE ON OBJECT::Sales_SP TO Mary; GO The REVOKE Transact-SQL statement is used to remove a previously assigned granted or denied permission.The REVOKE Transact-SQL statement does not prevent a principal from inheriting permissions through group orrole membership. You should not run the following Transact-SQL script: USE SalesDB; DENY EXECUTE ON OBJECT::SalesDB.Sales_SP TO Mary; GO The OBJECT clause requires the following syntax: [ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,…n ] ) ] Therefore, you should specify the complete path for the Sales_SP stored procedure, which in this scenario is Sales.Sales_SP , not SalesDB.Sales_SP .

Objective:
Managing SQL Server Security

Sub-Objective:
Manage database 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 > Deny (Transact-SQL) > DENY Object Permissions (Transact-SQL)