Granting Access to a Database Object
Applies To: SQL Server 2016
As an administrator, you can execute the SELECT from the Products table and the vw_Names view, and execute the pr_Names procedure; however, Mary cannot. To grant Mary the necessary permissions, use the GRANT statement.
Execute the following statement to give
EXECUTEpermission for the
GRANT EXECUTE ON pr_Names TO Mary; GO
In this scenario, Mary can only access the Products table by using the stored procedure. If you want Mary to be able to execute a SELECT statement against the view, then you must also execute
GRANT SELECT ON vw_Names TO Mary. To remove access to database objects, use the REVOKE statement.
You must have EXECUTE permission to execute a stored procedure. You must have SELECT, INSERT, UPDATE, and DELETE permissions to access and change data. The GRANT statement is also used for other permissions, such as permission to create tables.