Understand Grant Execute and Grant Select Permissions on SQL Server with Example

Consider  following Scenario

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you use?

A

GRANT SELECT ON Purchases.Suppliers TO ContosoUser

B

CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
WITH EXECUTE AS USER = 'dbo'
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser

C

CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

D

GRANT DELETE ON Purchases.Suppliers TO ContosoUser

Solution:

see sample table

Initial properties of ContosoUser see below

Initial State we can see user cant able to select table see below of that user

let see option A

  GRANT SELECT ON dbo.Suppliers TO ContosoUser 

if issue option A ContosoUser can only select that table so it cant use to delete
so it is not correct answer

 let see option B

it gives error

 let see option C

 After Execute SP of ContosoUser it is working fine see below

so OPTION C is the correct Answer

let see OPTION D


    GRANT DELETE ON dbo.Suppliers TO ContosoUser 

after that above command if run below commands

Delete from Suppliers where IsActive=0

If you execute it will throw message it needs additional select permission

 you need select permission on that table as see in option A

so option d is not correct answer it will be correct but you need to execute both OPTION A and OPTION D concurrently.

additional info if you need to get back permission you can use below T-SQL

    REVOKE SELECT ON dbo.Suppliers TO ContosoUser 

   REVOKE DELETE ON dbo.Suppliers TO ContosoUser 

   REVOKE EXECUTE ON dbo.PurgeInactiveSuppliers TO ContosoUser 

Also ContosoUser have only execute permission so ContosoUser user can't able to alter it

 Also you can see TBK2012Reader user can able to execute storedprocedure as see below

WITH EXECUTE AS CALLER clause

CREATE PROCEDURE dbo.PurgeInactiveSuppliersWITHCaller

        WITH EXECUTE AS CALLER

        as

    DELETE FROM dbo.Suppliers WHERE IsActive = 0

    GO

        GRANT EXEC ON dbo.PurgeInactiveSuppliersWITHCaller TO [TBK2012Reader]
GO

 Now TBK2012 Can able to remove that isactive field as see below

The EXECUTE AS clause can be added to stored procedures, functions, DML triggers, DDL triggers, queues as well as a stand alone clause to change the users context.  This clause is simply added to the code as follows:

CREATE PROCEDURE dbo.TestProcedure WITH EXECUTE AS OWNER

There are basically five types of impersonation that can be used:

• SELF - the specified user is the person creating or altering the module
• CALLER - this will take on the permissions of the current user
• OWNER - this will take on the permissions of the owner of the module being called
• 'user_name' - a specific user
• 'login_name' - a specific login

 CREATE PROCEDURE dbo.TestProcedure
 WITH EXECUTE AS OWNER
AS
BEGIN
 SQL Statement
END