Question

A developer needs permission to run every stored procedure in the Reporting schema of an Azure SQL Database, but must not be able to read or modify the underlying tables directly. New stored procedures are added to the Reporting schema frequently, and you want to avoid granting permissions procedure-by-procedure. Following least-privilege principles, which T-SQL statement should you execute?

Accepted Answer

GRANT EXECUTE ON SCHEMA::Reporting TO [developer];

Answer

ALTER ROLE db_datareader ADD MEMBER [developer];

Answer

GRANT CONTROL ON SCHEMA::Reporting TO [developer];

Answer

GRANT EXECUTE ON DATABASE::ReportingDB TO [developer];