The Meaning of Stored Procedures Part 7 — No direct owners, name resolutions and the difference between EXECUTE AS SELF and EXECUTE AS OWNER

There is surprisingly much misunderstanding about schema-user separation, and about how name resolution as well as impersonation work in SQL Server. The ability to impersonate users by code modules was added in SQL Server 2005, along with database schemas (SQL Server 2000 only allowed context switching by executing SETUSER statement), but those features are still [...]

The Meaning of Stored Procedures. Part 3 – Avoiding recompilations due to plan optimality-related reasons.

As you recall, in previous parts of this series we agreed that reusing execute plans might have tremendous impact on SQL Server performance, and saw how to avoid unnecessary recompilation by proper use of temporary tables and not changing cache-key SET options. If you missed those articles, they can be found here: The Meaning of [...]

The Meaning of Stored Procedures. Part 2 — Avoiding recompilations due to plan stability-related reasons.

In the previous article I tried to convince you that stored procedures are a proper way to avoid unnecessary compilations and recompilations. They may not be the fastest (in most cases prepared queries will be faster), but they are widely used, mainly because they are great from security and manageability perspectives. Unfortunately, stored procedures are [...]

The Meaning of Stored Procedures. Part 1 — Plan Caching and Reuse is a Good Thing.

Everybody knows that use of stored procedures offers a number of benefits over issuing T-SQL code directly from an application. One of the most common reasons for using stored procedures is as a security boundary — a user can be given access to execute a stored procedure without having permissions directly on the underlying objects. [...]