Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "GRANT EXECUTE" Statements - Granting EXECUTE permission

By: FYIcenter.com

(Continued from previous topic...)

How to grant a permission using "GRANT EXECUTE" statements?

This is the fourth tutorial of a quick lesson on creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.

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.

Procedure Title - Execute the following statement to give Mary the EXECUTE permission for the pr_Names stored procedure.

 
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.

Note: If the table, the view, and the stored procedure are not owned by the same schema, granting permissions becomes more complex. For information about how to configure permissions on objects with different owners, see Ownership Chains.

About GRANT 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.

(Continued on next topic...)

  1. How to use Transact-SQL statements to access the database engine?
  2. How to create new databases with "CREATE DATABASE" statements?
  3. How to create new table with "CREATE TABLE" statements?
  4. How to insert and update data into a table with "INSERT" and "UPDATE" statements?
  5. How to read data in a table with "SELECT" statements?
  6. How to create a login to access the database engine using "CREATE LOGIN" statements?
  7. How to create a user to access a database using "CREATE USER" statements?
  8. How to create a view and a stored procedure using "CREATE VIEW/PROCEDURE" statements?
  9. How to grant a permission using "GRANT EXECUTE" statements?
  10. How to delete database objects with "DROP" statements?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...