Using EXECUTE AS to control data access

17 Apr

Introduction

Ownership chaining is one process that SQL Server uses to allow stored procedures access to tables where the user might not have permission. It has issues with stored procedures that have dynamic sql – EXECUTE AS is one solution. Continue reading

.Net 3.5 Fails to Install with SQL 2012 on Windows 10

27 Mar

Background
Whilst installing SQL Server 2012 onto a new Windows 10 laptop, the SQL 2012 installation returned an error 0x800F081F.
Continue reading

Automatic Data Purging With Partition Truncation

21 Feb

Some years ago a wrote an article about automatic data purging with partition switching. Having moved to SQL Server 2016 recently I have encountered the new ability to truncate individual partitions, rather than switching data out.
Continue reading

Shredding JSON Nested Arrays

5 Feb

Whilst reading up on SQL Server 2016 JSON functionality I have seen many examples of extracting data from a JSON array. However, I wanted to work out how to extract data from an array within an array – for example, an array of customer data where each customer has an array of order details.
Continue reading

SESSION_CONTEXT – Basic Examples

1 Feb

Background
For a long time, web developers have been able to store session information. SQL Server has CONTEXT_INFO, which is a very poor implementation of a session variable. However, with SQL Server 2016 there is now a far more flexible SESSION_CONTEXT feature available.
Continue reading

TABLESAMPLE – Basic Examples

24 Jan

Whilst reading an article on Quora I saw mention of TABLESAMPLE. I had never heard of it and upon investigation it piqued my interest.
Continue reading

SSIS Project with ZappySys fails when upgraded from SQL 2012 to SQL 2016

22 Jan

Subtitled – And I can’t edit them with VS 2010 either.

Background

I am currently testing our upgrade process, for moving all of our databases from SQL Server 2012 instances to SQL Server 2016. The SSIS projects that use the ZappySys PowerPack tools are failing with a particularly unhelpful message. In addition, attempts to open those projects with SQL Server Data Tools 2010 fails – with an equally unhelpful message. This article records what I did to remedy this situation. Continue reading