Echoes

This second article in a two-part series shows you how to control the execution context of stored procedures. This time, it’s by using digital signatures and code signing.

During a recent consulting engagement, I noticed that the client needed to control the security context a stored procedure was running under but was using a convoluted method to do so. Changing the security context that stored procedures run under is a common requirement, letting users execute code via stored procedures that they aren’t allowed to execute directly.

2025-11-10

This is part 1 of a two-part series that shows you how to use the EXECUTE AS clause to change and control security context for stored procedures. Up next month will be digital signatures.

During a recent consulting engagement, I noticed that the client needed to control the security context that a stored procedure was running under but was using a convoluted method to do so. Changing the security context that stored procedures run under is a common requirement, letting users execute code via stored procedures that they aren’t allowed to execute directly.

2025-11-06

Uniqueness can be implemented by primary keys, unique constraints and unique indexes. The use of primary keys is well understood but confusion exists about unique constraints and unique indexes.

This issue arose from a consulting engagement I was performing earlier in the month. During one of the architectural discussions I was having with the client, an interesting question was suddenly posed: When would you ever use a unique index?

After thinking about this for a moment, I suddenly realized that I almost never would use one and so I raised the topic on a technical discussion forum. An interesting discussion ensued and I’ve summarized the key thoughts in this article.

2025-11-02

You shouldn’t have to know how a view or stored procedure works or its internal code structure to use it. To that end, it’s time that stored procedures expose detailed contracts.

Many DBAs don’t allow users direct access to tables in SQL Server databases and instead give them access only via a series of views and stored procedures. One of the cited benefits of this approach—beyond providing a security boundary—is that the views and stored procedures provide a layer of abstraction over the underlying data.

2025-10-29

It’s really common to see questions in the newsgroups about the inability to access a database that has been moved between servers. Invariably, the problem is that a SQL Server login (i.e., not a Windows login) can’t access the database.

By default, when you recreate a SQL Server login, you get a new security ID (SID), even though you have the same user name and password.

The problem then comes when you restore a database from another server. You can’t access it. If you try to create the user entry in the database, it says it already exists and fails. But if you try to list the users in the database, it also doesn’t show it. That’s often the point at which the administrator wants to throw their mouse through the screen but it doesn’t have to be a problem.

2025-10-25

We spend a lot of time doing performance tuning work for clients. Inappropriate indexing strategy is often high on the list of issues that we identify. Most of these issues can’t be fixed by SQL Server itself and require a detailed understanding of both how the tables are structured and how they are accessed.

However, there is one very common problem however that I believe SQL Server could assist with.

2025-10-21