Sql-Server

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: Security Level: Medium

Question:

Is it possible that revoking a permission can cause a user to be able to access a resource, when they previously could not?

If so, how?

Answer:

REVOKE can remove either a GRANT or a DENY. Revoking a DENY can cause a user to be able to access a resource that they previously could not.

2025-08-28

It’s likely that everyone who uses SSMS knows how to tell how long a query ran for. You can see it in the bottom right of the status bar when a query finishes.

But one question that often comes up with a long-running query is when did my query finish?

That’s not in the status bar and many people don’t seem to be aware that you can determine that.

It’s part of the data in the Properties window. So when you come to a query window where the query has finished, and you’re wondering when it finished, hit F4 (or right-click in the window and click Properties), and you’ll see this info shown:

2025-08-27

This is probably one of the simplest tips that I’ve published, yet I’m endlessly surprised how many people do not realize that it’s available.

When you have a script loaded in SSMS, and you execute the script, you might run into an error like this:

To find where the error is, just double-click the error down in the Messages tab. I double-clicked it, and it took me directly to the error and highlighted it:

2025-08-25

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:

In SQL Server, what is an orphaned user?

How do users typically become orphaned?

Answer:

An orphaned user is one that appears in a database, but the server principal that they are linked to, does not exist.

2025-08-24

Most people use SSMS to edit SQL queries. No big surprise there. The files will have a file type of .sql.

But what many people don’t understand is that SSMS inherits many of its underlying Visual Studio’s abilities to edit other document types.

For example, if you open a .txt text file, you can edit it just fine, and you can also include files like this in SSMS script projects. That can be useful for additional notes and documentation.

2025-08-23

SQL Server 2008 added the ability to work with spatial data by the additional of the geometry and geography data types. When they first were added, there was no tools support for working with them, and all we had was direct manipulation of their internal binary storage.

Here’s an example:

I’ve defined a variable named @Shape of type GEOMETRY. I’ve then assigned a shape to it, based on a polygon formed by a set of points. If you look carefully, you’ll notice that it’s a square.

2025-08-21

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: Development Level: Advanced

Question:

Is dynamic data masking a form of encryption?

Answer:

No. Dynamic data masking just obfuscates the data when it is presented to end users. It is unrelated to encryption.

Obfuscation is not a form of encryption.

2025-08-20

When you right-click a table in SQL Server Management Studio, you get options for selecting or editing but the number of rows is limited:

Those values can be changed. By default, these numbers are both 200, but I’ve decided to change the default number of rows selected to 1000.

In Tools, Options, SQL Server Object Explorer, then Commands, you can set the values to whatever suits you:

I don’t tend to ever use the Edit option but I’d suggest not making it too large.

2025-08-19

I recently wrote about the need for T-SQL to have constants. I received a lot of feedback on that.

Everyone seemed to agree, although one or two asked Why bother as Microsoft isn’t really listening now about enhancing T-SQL?. I understand that sentiment but I don’t think things are quite as bleak as some are suggesting. I currently sense a stirring within the product group, where there is a new interest in developers.

2025-08-18

This is a quick tip but an important one. I see many people using SSMS and they aren’t aware of Activity Monitor.

While there are many clever things that we can do with queries, to interrogate the health of the system, don’t forget that there is quite a bit of useful information in Activity Monitor, and it’s easy to get to.

There are two basic ways to launch Activity Monitor. The first is to right-click the server in Object Explorer:

2025-08-17