A fundamental way that SQL Server determines whether a principal has the permissions necessary to execute code is with its execution context rules. It’s all complicated by the possibility that a principal has permission to execute code but doesn’t have permission on the underlying objects accessed by the code, such as the data in a table. This stairway level will explore SQL Server’s execution context, ownership chains, and impersonation, as well as show you how you can control access to data via T-SQL code.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2015-02-04

6,145 reads

Discuss

All authors

Read the latest Blogs

Visit the forum

Question of the Day

The Tightly Linked View

I try to run this code on SQL Server 2022. All the objects exist in the database.

CREATE OR ALTER VIEW OrderShipping
AS
SELECT cl.CityNameID,
       cl.CityName,
       o.OrderID,
       o.Customer,
       o.OrderDate,
       o.CustomerID,
       o.cityId
 FROM dbo.CityList AS cl
 INNER JOIN dbo.[Order] AS o ON o.cityId = cl.CityNameID
GO
CREATE OR ALTER FUNCTION GetShipCityForOrder
(
    @OrderID INT
)
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @city VARCHAR(50);
    SELECT @city = os.CityName
    FROM dbo.OrderShipping AS os
    WHERE os.OrderID = @OrderID;
    RETURN @city;
END;
go

What is the result?

See possible answers

Stairway to SQL Server Security Level 6: Execution Context and Code Signing

Blogs

The Book of Redgate: We Value Teams

Troubleshooting TempDB Log Full Errors When SSMS Won’t Connect

Accelerating AI with Confidence: Why Microsoft Purview is Key to Responsible Innovation

Forums

Planning for tomorrow, today - database migrations

Bottlenecks on SQL Server performance

Is there some good routines for updating SQL Server database objects with GitHub

Question of the Day

The Tightly Linked View