USE HINT In SQL Server 2016 SP1

Lori Brown documents the hints available with SQL Server 2016 SP1’s USE HINT syntax:

USE HINT ( hint_name ) Provides one or more additional hints to the query processor as specified by a hint name inside single quotation marks. Hint names are case-insensitive. USE HINT can be utilized without having to be a member of the sysadmin server role.

It’s an interesting list.

Getting Effective Permissions

Jana Sattainathan explains how to get a user’s effective permissions:

Microsoft has provided a function named fn_my_permissions that gets the permissions for you. It has multiple classes of objects for which you can get permissions. We already know about SERVER, DATABASE and OBJECT but there are a lot more. To get that list of classes, let us use fn_builtin_permissions function

The one thing I wish this function did was let a sysadmin see another user’s effective permissions without using EXECUTE AS; I’d like to be able to apply that function to sys.logins and get an exploded list for reporting.

Upgrading SSRS From 2008R2 To 2014

Dave Turpin needed to perform an upgrade of a Reporting Services installation:

So let me share with you my biggest take away from this project:  EVERYTHING ABOUT USER CREATED REPORTS IS STORED IN THE SQL Server Reporting databases.  So if you are reading this post you probably are about to move an instance of SSRS, and may be concerned about the many, many reports involved.

Based on my one experience with this, there is need to move individual reports.  If you follow the process carefully, all of the existing reports will be re-created on the new machine.  It’s not quite magic, but it sure feels like it when everything shows up on the new system.

Read on for the solution Dave came up with.

Basics Of Azure Analysis Services Management

Bill Anton walks through some of the basics of managing an Azure Analysis Services cube:

The quickest win – from an ROI perspective – for Azure AS is the ability to pause the instance during extended periods of inactivity – for example, at night, when there aren’t any users running reports.

This can be achieved via the Suspend-AzureRmAnalysisServicesServer cmdlet we saw in the previous post.

Read on for a few tips of this ilk, including resizing the server.

Joins Galore

Kevin Feasel



Lukas Eder has a comprehensive guide to joining data using SQL:

Alternative syntaxes: NATURAL JOIN

An more extreme and much less useful form of "EQUI" JOIN is the NATURAL JOIN clause. The previous example could be further “improved” by replacing USING by NATURAL JOIN like this:

FROM actor
NATURAL JOIN film_actor

Notice how we no longer need to specify any JOIN criteria, because a NATURAL JOIN will automatically take all the columns that share the same name from both tables that it joins and place them in a “hidden” USING clause. As we’ve seen before, as primary keys and foreign keys have the same column name, this appears quite useful.

There is a high likelihood that you will learn at least one new thing here; for example, check out lateral joins (which SQL Server practitioners know as something else).

Turning On SQL Authentication

Kenneth Fisher hits a frequent cause of login failure:

No go. Next thing to check is the password. The error I’m getting does indicate the incorrect password so it’s a distinct possibility. Now when I moved the server principal I made sure to copy the password hash from the old server so the password should be the same. However, I’ve made mistakes before, and odd things happen, so I decided to change the password just in case. Before I did I backed up the original password just in case.

The solution is something that I’ve seen a lot of sysadmins forget to do in their setup processes.


January 2017
« Dec Feb »