SQL Server - Procedure permissions

 

CREATE PROCEDURE Purchasing.uspVendorALLInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor,
           p.Name AS 'Product Name',
           v.CreditRating AS Rating,
           v.ActiveFlag AS Availability
      FROM Purchasing.Vendor v
     INNER JOIN Purchasing.ProductVendor pv
        ON v.BusinessEntityID = pv.BusinessEntityID
     INNER JOIN Production.Product p
        ON pv.ProductID = p.ProductID
     ORDER BY v.Name;
GO

For the above SQL Server procedure, what does the 'WITH EXECUTE AS CALLER' mean?

 

In SQL Server, the WITH EXECUTE AS clause determines the security context (the permissions) used when the stored procedure is running.

WITH EXECUTE AS CALLER is actually the default behavior for all modules. It means that the procedure will run using the permissions of the person who is currently executing the code.

---

How it Works

When a user calls Purchasing.uspVendorALLInfo, SQL Server checks two things:

1. Execution Permission: Does the user have permission to run the procedure itself?

2. Data Permissions: Does the user have permission to access the underlying tables (Purchasing.Vendor, Purchasing.ProductVendor, and Production.Product)?

If the user has permission to run the procedure but does not have SELECT permissions on the Vendor table, the query will fail.

Comparison with Other Options

Option	Who provides the permissions?
CALLER	The User running the procedure.
OWNER	The person who owns the procedure (usually the schema owner).
SELF	The person creating or altering the procedure.
'user_name'	A specific named user defined in the database.

---

Why use CALLER?

Even though it is the default, explicitly typing WITH EXECUTE AS CALLER is a good practice for:

• Documentation: It makes it clear to other developers that no special permission "masking" is happening.

• Security: It follows the "Principle of Least Privilege," ensuring that users can't see data through a procedure that they couldn't see by writing a manual query.

The "Ownership Chaining" Exception

There is one unique rule in SQL Server called Ownership Chaining.

If the same user (usually dbo) owns both the Stored Procedure and the Tables, SQL Server only checks if the user has permission to execute the procedure. It will skip checking permissions on the tables.

EXECUTE AS CALLER respects this chain. If the owners are different, the caller must have explicit rights to everything.

 

 

ALTER PROCEDURE Purchasing.uspVendorALLInfo
    @Product varchar(25)
AS
    SET NOCOUNT ON;
    
    SELECT LEFT(v.Name, 25) AS Vendor,
           LEFT(p.Name, 25) AS 'Product Name',
           CASE v.CreditRating
               WHEN 1 THEN 'Superior'
               WHEN 2 THEN 'Excellent'
               WHEN 3 THEN 'Above average'
               WHEN 4 THEN 'Average'
               WHEN 5 THEN 'Below Average'
               ELSE 'No rating'
           END AS Rating,
           CASE v.ActiveFlag
               WHEN 1 THEN 'Yes'
               ELSE 'No'
           END AS Availability
      FROM Purchasing.Vendor AS v
     INNER JOIN Purchasing.ProductVendor AS pv
        ON v.BusinessEntityID = pv.BusinessEntityID
     INNER JOIN Production.Product AS p
        ON pv.ProductID = p.ProductID
     WHERE p.Name LIKE @Product;
GO