Azure-Sql-Db

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

One of the bigger changes in recent versions of SSMS has been the inclusion of connection encryption details on the main connection page:

Previously, this was on another page of the logon dialog. Once you make the connection though, there previously was no indication of what type of encryption was used. Now that appears in the query status bar as a lock. When you hover over the lock, it shows you the type of encryption that had been selected.

2025-11-04

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

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Medium

Question:

You are trying to truncate a table but the command is failing.

Can you give some examples of what can cause a truncate command on a table to fail?

Answer:

One common reason is that the table is referenced by a FOREIGN KEY constraint. (But you can truncate a table that has a foreign key that references itself).

2025-10-31

When I’m editing large script files in SSMS, I sometimes find that the scrolling speed of my mouse wheel is too fast or too slow.

If you don’t like the current speed, SSMS has an option to adjust that.

It’s in the Text Editor, Advanced, section. It allows you to control how many lines you get per scroll (for vertical scrolling) or how many characters (for horizontal scrolling):

2025-10-30

When I write T-SQL, my standard is to use upper-case names for built-in system functions. But not everyone likes that.

I’ve done a lot of work on systems where all these names are lower-case. I don’t have any great objection to that, but it’s painful if Intellisense keeps making them upper-case.

But there is a solution. In Text Editor, Transact-SQL, then Intellisense, there is an option for this:

2025-10-28

When you close SSMS, the default action is that it checks each open window and makes sure there are no open transactions before it closes.

This is to allow you to decide what to do if you do have an open transaction, and based on that, to avoid losing any work.

While this sounds like a great idea, I mostly now work with Azure SQL Database, and my SSMS windows have been idle for long enough that I’ve lost the connection. So, when I close SSMS, it tries to check the transaction status against connections that are already closed under the covers.

2025-10-26

When I first installed v21 of SSMS, I was puzzled that many of the connection options in Object Explorer were greyed out:

Because I had been working on a preview version, I thought it just wasn’t there yet. But when I checked the release notes, I saw it should be there. The issue is that I hadn’t added the Business Intelligence workload.

You don’t do that from within SSMS. Instead, you need to run the Visual Studio Installer:

2025-10-24

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Advanced

Question:

Several trace flags are in use on a server you manage.

You are concerned that a behavior that you are seeing in a session is being caused by an enabled trace flag. How can you determine which trace flags are enabled for a session?

2025-10-23

I’ve been learning Mandarin (Chinese) for many, many years. I’m determined to become as fluent in speaking as I can, but to also be reasonable at reading and writing.

One important aspect of doing this, is to make sure you use the language you are learning as much as possible.

A great advantage of SSMS is that it is available for use in many languages. Yet when you do this, SSMS ends up supporting both the target language and English. That means that I can work part of the time in Chinese (to force myself to learn) and the rest of the time in English.

2025-10-22