SQL: Make sure to use ORIGINAL_LOGIN when auditing

I regularly see code where SQL Server DBAs and developers are trying to log which user/login took a particular action within the database. There are many functions which appear to return the information needed, but there's only one that should normally be used: ORIGINAL_LOGIN().

A login is the way that a connection is authenticated to the server ie: it's the "who are you?" at the server level.

Most times, a user is a mapping of that login to a particular database.  The login and user will often have the same name (and I'd recommend that you do that to avoid confusion) but they do not have to be. A login Terry could be a user Mary in one database and a user Nga in another database.

A user can also represent the role that a login plays in the database. This is commonly see with administrators who will appear as dbo (ie: database owner) within each database, rather than their actual names.

So, using a user name within a database for auditing is normally not appropriate. 

A further complication is that a security context can be changed by impersonation. For example, a user might execute a procedure that has a WITH EXECUTE AS clause. Functions like USER_NAME() will return the impersonated context, not the actual context.

What you normally really want is the actual person who has logged in. ORIGINAL_LOGIN() goes to the bottom of the security stack, and returns that value. It's typically what I use for any type of auditing.

You can see its action in the main image above. Note what happens in the second (impersonated) context.

 

 

 

Shortcut: Viewing client statistics in SQL Server Management Studio

While SQL Server is quite fast at executing queries, when you are connecting from a client application like SQL Server Management Studio (SSMS), you might wonder how much time SQL Server spent executing the query, as opposed to how long the communication with the server took.

This type of information is available in the Client Statistics.

Let's see an example. If I connect to a server in an Azure data center, I'll have higher latency than for one in my own site. That will affect the wait time for a server response.

This server is in the Melbourne (Australia South East) data center.

Let's execute a simple query against it, but before doing so, on the Query menu, choose Include Client Statistics.

I'll just query the SQL Server version:

Notice that an extra tab of data is returned.

From the bottom section of this tab, we can see where the time was spent. In this case, out of a total of 34 milliseconds for the query, 33 milliseconds was spent waiting for the server.

SDU Tools: Extract Trimmed Words from T-SQL Strings

Occasionally I've needed to take a string, and extract all the words out of it. For example a string like 'hello        there     greg' might lead me to want the three words 'hello', 'there', and 'greg'. Note that I usually want them trimmed, not just extracted.

In our free SDU Tools for developers and DBAs, we added a table-valued function ExtractTrimmedWords to help with this. You can pass it a string, and it will pull it apart for you, assuming that you have whitespace separating the words.

We use space, tab, carriage return, and line feed as whitespace characters for separating words.

The main image above shows it in use. As well as returning the words, we decided to return a WordNumber column as well, in case the ordering of the words matter to you.

I wish Microsoft had done that with their STRING_SPLIT function. (And we added that in our SplitDelimitedString function).

You can see ExtractTrimmedWords in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

SQL: The T-SQL SIGN function and what's in a return type?

When you've worked with a product like SQL Server for a long time, and more importantly, are one of the odd people who've read a great amount of the documentation simply for interest, it feels really strange to come across a basic function that you'd never noticed before. That's how I felt when someone mentioned the T-SQL SIGN function.

I thought, "the what function??".

Now it works pretty much as you'd expect. It returns:

  • +1 for positive numbers
  • 0 for zero values
  • -1 for negative numbers
  • NULL for NULL values

No surprises there and you can see that in the main picture above.

What I wasn't expecting (and have to say if I was creating it that I would not have done), was the output data types. Here are the values returned:

I don't get why the return type has been designed to match the input type. It seems to me that a value indicating positive, zero, or negative should really have a fixed data type ie: int.

Regardless, I was also intrigued by the "Other types" going to float. It's not all types, as the value appears to need to be directly castable to float:

I tried other numeric data types to see what happens. I pushed a set of them into a temporary table:

And then checked the column data types that were returned:

It really does try to mostly match the input data type. I was mostly interested to see if decimal values would match the precision and scale, and they do.

There must have been some logic for the varying output data types, and that means there must have been some envisaged functionality beyond just indicating the sign. I'd love to hear from any of you if you have any ideas on how the varying output data types for this could be used in a practical way.

The online documentation also says "from SQL Server 2008" but that's just the oldest supported version anyway. Anyone know what version this was first introduced in?

 

 

New online on-demand SQL Server courses from SQL Down Under

Hi Folks,

We have a whole series of online and on-demand courses coming. The first two of these are available right now.

The good news? The first one is free and the second one has a big introductory discount.

The first course 4 Steps to Faster SQL Server Applications is a short course for developers, new DBAs, and testers, etc. who don't know anything much about tuning SQL Server applications. It focuses on finding and fixing the most problematic queries, either in terms of index tuning, or removing repetitive queries, all using free tools.

Please pass details of this course onto any developers, new DBAs, or testers that you know who might benefit from it.

The second course is an in-depth look at core SQL Server indexing concepts called Designing Effective Indexes for SQL Server. It's $295 USD (plus VAT if applicable) but coupon code INDEXINTRO will knock 30% off that until September 30th.

And more courses coming online very soon. You'll find them all at:

https://training.sqldownunder.com

 

Shortcut: Adding additional parameters to connections in SSMS

When I am writing my own code using a .NET (or other) language, I have a great deal of control of how the connection string that my application uses to connect to SQL Server is configured.

In particular, I might need to add another parameter or two.

As a simple example, you might have a multi-subnet Availability Group, spread across a production site and a disaster recovery site. It's common to then have an Availability Group Listener in both subnets.

If you add the parameter MultiSubnetFailover=true to your connection string, when SQL Server attempts to connect to the listener, it will send a request to each IP address concurrently, not just to one at a time. It will then connect to whichever server responds first.

This is great, but how do we do that with SQL Server Management Studio (SSMS) connections?

The answer is that in the database server connection dialog, we can choose Options:

In the dialog that appears, there is a tab for Additional Connection Parameters:

On that tab, I can enter the required details:

Note also that if you enter a value here that is also on the graphical connection pages, the value that you enter overrides those values.

SDU Tools: ExecuteOrPrint – Printing large strings in T-SQL

The PRINT statement in SQL Server's T-SQL language is useful but one of the biggest restrictions with it is the size of the strings that it can print. Where this becomes a big issue is if you are needing to create dynamic SQL statements (which you obviously need to be careful of in the first place) or scripting database objects, and the statements need to be either executed or printed.

In our free SDU Tools for developers and DBAs, we added a procedure ExecuteOrPrint to help with this. You can pass it a large string (nvarchar(max) typically) and tell it to either execute the value, or if you are using it for scripting or debugging, to print the value.

The default action is to print the value.

We designed it to help with both scenarios in the same code. For example, in scripting, you might want batch separators (ie: GO) but when executing, you don't want to send those to the server, you want to carve up the script and send it in batches, based upon the separator. It can also add carriage returns and line feeds as required.

The parameters are as follows:

@StringToExecuteOrPrint nvarchar(max) -> String containing SQL commands
@PrintOnly bit = 1 -> If set to 1 commands are printed only not executed
@NumberOfCrLfBeforeGO int = 0 -> Number of carriage return linefeeds added before the batch separator (normally GO)
@IncludeGO bit = 0 -> If 1 the batch separator (normally GO) will be added
@NumberOfCrLfAfterGO int = 0 -> Number of carriage return linefeeds added after the batch separator (normally GO)
@BatchSeparator nvarchar(20) = N'GO' -> Batch separator to use (defaults to GO)

You can also see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

SQL: Use elevated procedure permissions instead of elevated user permissions

Choosing the right database permission can be hard. I've lost count of the number of times I've heard a discussion like this:

I need to let Mary restore truncate one of the tables but I don't want to give her permission to do it, in case she stuffs it up.

or

I need to let Paul restore this database but I don't want him to be able to restore other databases, and I'm worried if I give him the permission, he might accidentally do something bad and I'll be blamed for it.

Whenever you have this type of discussion, the problem is that you're looking to give the user a permission, but only in a very limited situation and the DCL (data control language) statements (ie: GRANT, DENY, REVOKE) are too coarse for what you're trying to do.

Instead, what you need to do is to create a stored procedure, to give the stored procedure the permission to do what's needed, and then just give the user permission to execute the stored procedure.

There are two basic ways to do this.

The first is to create the stored procedure with a WITH EXECUTE AS clause. For example, I if write this:

CREATE PROCEDURE Utility.DoSomethingPotentiallyScary
WITH EXECUTE AS OWNER
AS

then whatever the procedure does is executed as the owner, not as the user. And this includes any dynamic SQL code. It's documented here. For stored procedures, instead of OWNER, you can also have CALLER (that's the default anyway), SELF (ie: the person creating the procedure), or a specific user.

To create or alter a procedure to execute as someone else, you need to have IMPERSONATE permission on that user. (That's already there if you're an admin).

That's a pretty simple solution but it has a few limitations.

For trickier scenarios (such as some cross-database scenarios), you can do this instead:

  • Create a certificate
  • Create a user from that certificate
  • Add the required permissions to that special user
  • Digitally sign the stored procedure with the certificate

Now when the stored procedure runs, it will acquire the permissions associated with that certificate, but only while it runs. An added bonus is that if the stored procedure is changed in any way, the digital signature is removed, along with the permissions.

 

Upcoming: User Group Tour in New Zealand soon

Hi Folks, we're looking forward to doing a number of presentations across New Zealand starting around the end of this month.

Aug 27th (Mon): SQL Server User Group – Wellington (Things I wish Developers knew about SQL Server)
Details here

Aug 28th (Tue): Data Management and Analytics Meetup – Wellington (A Comprehensive Look at What's New in SQL Server 2017 – and ongoing product directions)
Details here

Aug 31st (Fri): Pre-conference Day  for SQL Saturday – Auckland (Developing SQL Server Applications that Perform)
Register here

Sep 1st (Sat): SQL Saturday – Auckland (Keynote)
Register here

Sep 1st (Sat): SQL Saturday – Auckland (Database on a diet – taming a large database)
Register here

Sep 3rd (Mon): SQL SERVER & Data Management User Group – Christchurch (Things I wish Developers knew about SQL Server)
Details here

Sept 6th (Thu): Venue TBA – Dunedin (Database on a diet – taming a large database)

Would really love to see you at any of them. Please come and say hi.

Shortcut: Using "surrounds with" snippets in SQL Server Management Studio

In previous posts, I've been talking about how to use snippets in SQL Server Management Studio (SSMS) and how to create your own. There are several types of snippets and one of the special types of snippets that I want to mention are the "surround with" snippets.

If you look at the following block of code:

Imagine that you want to execute the four highlighted lines only when a condition is true. If I hit Ctrl-K and Ctrl-S while they are highlighted, I'm prompted with this:

Note that I get an option to surround them with a BEGIN/END, or an IF or WHILE. Let's choose an IF. I just double-click the If option and this happens:

We can then just type the condition. Notice that because I was using multiple lines, it put them all in a BEGIN/END block for me.

This is all quite good but I still might want to create my own instead, and I can do that. An example of why I might want to do that, is that I might want a statement terminator after the END.

If we go into the Code Snippets Manager (from the Tools menu), and expand the Function category, we can see this:

I can't say that I think of IF as a function but none-the-less, note that the type is a SurroundsWith snippet. Let's see how it's defined. The Location is here:

If I open that file in NotePad++, I see the following. Note how the value called $selected$ is enclosed within the snippet.

I could then either modify this one (probably not best), or use it as the basis of a new snippet for myself.