SQL Server Permissions and Security Principals at Houston SQL Saturday 2015

Thanks to everyone who attended my presentation on SQL Server Permissions and Security Principals this morning at Houston SQL Saturday 2015!

What an awesome event! Please join me in sending a note of thanks and congratulations to Nancy Hidy-Wilson and the rest of the Houston Area SQL Saturday User Group for organizing this awesome event.

My session was be a ground-floor introduction to SQL Server permissions starting with the basics and moving into the security implications behinds stored procedures, views, database ownership, application connections, contained databases, application roles and more.

Download my slides and sample scripts here:
http://1drv.ms/1FSdJvh

 

Error Upon First Processing Your SSAS Database With A Service Account

This blog post is to document the easy fix to a problem that has become a rite of passage for the good folks (including myself) on the data warehouse development team I've been working with recently.

Here's the error text you've probably received the first time you try to process your Analysis Services database using a job or, in this case, an SSIS package calling an Analysis Services Execute DDL Task:

Executing ExecutePackageTask: D:\whatever.dtsx
Error: 0xC1060000 at Analysis Services Processing Task, Analysis Services Execute DDL Task: OLE DB or ODBC error: Login failed for user 'NT SERVICE\MSOLAP$SQL2K14'.; 28000; Cannot open database "DataWhatever" requested by the login. The login failed.; 42000.
Error: 0xC1120064 at Analysis Services Processing Task, Analysis Services Execute DDL Task: A connection could not be made to the data source with the DataSourceID of '4c67b909-250d-4b14-95cc-451d1c95bc9f', Name of 'SqlServer .sql2k14bi DataWhatever'.
Error: 0xC11F0056 at Analysis Services Processing Task, Analysis Services Execute DDL Task: An error occurred while processing the partition 'DimDate_168cd22b-ae72-4730-a054-4fd0fa6acc2f' in table 'DimDate_168cd22b-ae72-4730-a054-4fd0fa6acc2f'.
Error: 0xC11C0006 at Analysis Services Processing Task, Analysis Services Execute DDL Task: The current operation was cancelled because another operation in the transaction failed.
Task failed: Analysis Services Processing Task
Warning: 0x80019002 at Main: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

The solution is quite simple.

The SSAS data cube might have been developed and processed just fine with your personal user account, but now that you've executed it with a service account for the first time, you need to grant it permissions. The SSAS Service account does not have rights to access your data source, in this case, your SQL Server 2014 instance.

Grant read-only permissions to the SSAS service account to the source database, for example, by creating a login and adding that login to the db_datareader role of the database, or granting SELECT permissions to the tables or schema needed to process the SSAS cube.

Behavior of ORDER BY + UNION Can Be Confusing

This is an interesting example and it exposes a quirk with the concept of a UNION, which is, that the various subsets of a UNION cannot be guaranteed to be sorted by their subset character.

The difference is important to understand - it is the difference between a result set being determinant (in this case, predictably ordered) or not. It's also the difference between a syntax error and a properly ordered UNION'd result set.

Here's the key: after two results sets have been UNION’d, you cannot independently sort the rows in the first query, then the results in the second query, because the origin rowset is lost.

Here's a demonstration:

DECLARE @tbl TABLE ( id INT primary key, val VARCHAR(10) )
INSERT @tbl
( id, val )
VALUES 
( 3, 'Last' ),
( 1, 'First' ),
( 2, 'Beta' )
 
-- Works
SELECT *
FROM (
  SELECT TOP 1 *
FROM @tbl t
ORDER BY t.id DESC
  UNION ALL
  SELECT TOP 1 *
FROM @tbl t
ORDER BY t.id DESC
) x

id val
3 Last
3 Last

-- Same intent, but throws a syntax error
SELECT TOP 1 *
FROM @tbl t
ORDER BY t.id DESC
UNION ALL
SELECT TOP 1 *
FROM @tbl t
ORDER BY t.id DESC

Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'UNION'.

-- Note that the ORDER BY is sorting the post-union result set, but that our TOP 1 is non-determinant.
SELECT TOP 1 *
FROM @tbl t

UNION ALL
SELECT TOP 1 *
FROM @tbl t
ORDER BY t.id DESC

id val
1 First
1 First

Note that the last line in last query, is not sorting the bottom reference to @tbl, but rather is sorting the post-union result set.

Look at that values we're getting in the last subquery. Why the value of 1?

Note that I have a primary key on the table variable, which by default creates a clustered index. This is not the "default sort order" of an unsorted query return, but in this particular and highly simplistic case, it does provide us a sort when getting a TOP 1. Without the PRIMARY KEY in the DECLARE, both of these valid-syntax queries return 3 and 3, because it's now an unsorted heap.

Move the PRIMARY KEY clustered index to the val column, and the last query results in 2 and 2, because 'Beta' is now the first row returned, when sorted (ascending, by default).

The default sort for an query, without an ORDER BY in a query, will be non-determinant from query to query. In some queries, a nonclustered index or a joined table or other operation could provide the sort. In order words, sort order without an ORDER BY may be sometimes predictable, not reliable.

So, why are ORDER BY's allowed after each UNION statement in a subquery?  The ordering of the post-union result set can now be provided with an ORDER BY outside of the subquery, so I’m speculating that the syntax allows it.  Inside a subquery, we can assume that the ORDER BY for each query in a UNION is actually not referring to the post-UNION result set.

That’s weird, but wrapping the union statement in a subquery is not “hiding an error” as it may appear to be, it’s actually allowing you to now define three different ORDER BY’s if you want it – one for each pre-union result set and one for the post-union set.

Without parenthesis, there’s no syntax to support those three different ORDER BY’s, there is only one post-union result set ORDER BY.

So, why make this a blog post?

Because one of the developers I work with found the last query above in legacy code for an application they are supporting. A real developer made the very real mistake of using a TOP without an ORDER BY in each half of a UNION (of two far more complicated queries than the above examples), resulting in unpredictable and inconsistent results that also failed to enforce the business logic that was required.

Remember, NEVER use a TOP without an ORDER BY, and be aware of how a UNION can make this tricky!

See You Next Weekend at SQL Saturday Houston!

Looking forward to speaking next weekend at SQL Saturday Houston 2015 on June 13!

If you haven't already registered for this great event, here's the link: https://www.sqlsaturday.com/408/registernow.aspx

This event will be held at San Jacinto College in Houston, and admittance to this event is free but there is a $10 lunch fee.

I'll be presenting on SQL Server Permissions and Security Principals at 9:45AM in room 1.115. My session, which I have done at other SQL Saturdays in the past, will be a ground-floor introduction to SQL Server permissions starting with the basics and moving into the security implications behinds stored procedures, views, database ownership, application connections, contained databases, application roles and more. This class is perfect for DBA's, developers and system admins.