As an administrator, you can execute the SELECT from the Components table and the v_Components view, and execute the usp_GetBatmobileComponentsList procedure; however, your new user cannot. To grant your new user the necessary permissions, use the GRANT statement.

Let’s assume for this lesson you’ve already created a SQL Server user [Lucius Fox].

Execute the following statement to give [Lucius Fox] the EXECUTE permission for the usp_GetBatmobileComponentsList stored procedure.

GRANT EXECUTE ON usp_GetBatmobileComponentsList TO [Lucius Fox];

In this scenario, [Lucius Fox] can only access the Components table by using the stored procedure. If you want [Lucius Fox] to be able to execute a SELECT statement against the view, then you must also execute

GRANT SELECT ON v_Components TO [Lucius Fox]

To remove access to database objects, use the REVOKE statement.

About GRANT

You must have SELECT, INSERT, UPDATE, and DELETE permissions to access and change data. You must have EXECUTE permission to execute a stored procedure. The GRANT statement is also used for other permissions, such as permission to create tables. If you’d like more information on the GRANT command, please visit Microsoft’s Books Online.

Logins give users permissions to connect to SQL Server. Users are logins that can access a specific database. Use the GRANT statement to give users permission to read and to access and change the data.

Now you know how to create logins, users, and GRANT specific permissions.  And knowing is half the battle.  Let me know if you have any more questions, and I can go all 80’s Public Service announcement on you!