A Fine Slice Of SQL Server
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.
Comments closedJana 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.
Comments closedDave 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.
Comments closedBill 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.
Comments closedLukas Eder has a comprehensive guide to joining data using SQL:
Alternative syntaxes: NATURAL JOIN
An more extreme and much less useful form of
"EQUI" JOINis theNATURAL JOINclause. The previous example could be further “improved” by replacingUSINGbyNATURAL JOINlike this:SELECT * FROM actor NATURAL JOIN film_actor NATURAL JOIN filmNotice how we no longer need to specify any
JOINcriteria, because aNATURAL JOINwill automatically take all the columns that share the same name from both tables that it joins and place them in a “hidden”USINGclause. 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).
Comments closedKenneth 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.
Comments closed