My new book hit No 1 hot new release

I am very excited that my new book, Expert Scripting and Automation has hit No 1 hot new release on Amazon.com.

Even more pleasing is that today I have seen the print copy for the first time!

Expert Scripting & Automation

I'm very please to announce that my new book, Expert Scripting and Automation for SQL Server DBAs has now been published.

It is already available on Amazon.com as both an e-book and a print book, and will hit the UK shelves in the coming week.

I don't believe that there is any other book quite like this one on the market, so I really hope you all enjoy the read!

Using Row-Level Security with HierarchyID

When I first heard about Row-Level Security (RSL), one of the first use cases I though of, was to satisfy queries against a hierarchical table. For example, imagine that you had a table of employees details; you could use RSL to limit the rows that each employee could view, to include only those employees who report to them.

So lets have a look at how you could achieve this, by using the HumanResources.Employee table in the AdventureWorks2016CTP3 database.

In order to implement RSL, we will need two objects. The first is a Security Predicate. This consists of an inline table-valued function, which determines if a row should be accessible. The second, is a Security Policy. The Security Policy is a new artifact type in SQL Server 2016, and binds the Security Predicate to a table.

The script below creates the Security Predicate in a schema named Security.

CREATEFUNCTION Security.fn_securitypredicate(@OrganizationNode HIERARCHYID)
    RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
FROM HumanResources.Employee e1
WHERE @OrganizationNode.IsDescendantOf(OrganizationNode) = 1
AND LoginID = 'adventure-works\' + SUSER_SNAME() ;
GO

The function accepts a single parameter, of the type HIERARCHYID. This is a complex data type, implemented through SQLCLR, which provides a number of methods, which can be used to determine position within a hierarchy, as well as hierarchy ancestry. A full method reference for the data type can be found at  https://msdn.microsoft.com/en-us/library/bb677193.aspx.

Using SCHEMABINDING on the function means that columns refferenced by the Security Predicate cannot be altered, but simplifies security administration, as the user who implicitly calls the function, will not need permissions to any tables or functions that are referenced by the Security Predicate.

The query uses the IsDecendantOf method, against the @OrganizationNode parameter (which will represent the OrganizationNode column of each row within the Employees table, to find all descendants of the row, where the LoginID column corresponds with the User ID of the user that has run the query. The concatenation of 'adventure-works\' is used to make the value returned by the SUSER_SNAME() function match the values stored in the table, where the domain of the user is also recorded. 1 is returned, for each row that matches the criteria, which tells the Security Policy that the row can be accessed.

The script below creates the Security Policy.

CREATE SECURITY POLICY Security.EmployeeSecurityPolicy
ADD FILTER PREDICATE Security.fn_securitypredicate(OrganizationNode) ON HumanResources.Employee
WITH (STATE=ON, SCHEMABINDING=ON) ;

The Security Policy is also created in the Security schema. Creating RSL objects in a separate schema is a best practice, as it simplifies security administration. The ADD FILTER PREDICATE syntax performs several functions.  

Firstly, it specifies that the predicate should be used to silently filter rows, as opposed to a BLOCK predicate, which will stop DML statements beling issued against rows, and return an error message.

Secondly, it binds the Security Predicate to the HumanResources.Employee table.

Thirdly, it passes the OrganizationNode column, from the HumanResources.Employee table, to the Security Predicate function.

The WITH statement specifies that both STATE and SCHEMABINDING are ON. STATE will determine if the Policy is enabled on creation. SCHEMABINDING will determine if a Security Predicate MUST use SCHEMABINDING, or if it is optional.

I talk more about RSL, in my upcoming book, Securing SQL Server, which will be published by Apress, in early 2017.

Use SQL 2016 Query Store to Remove Ad-hoc Plans

Ad-hoc query plans consume memory and can be of limited use. It is a good idea to remove ad-doc query plans if they are not being recused. The query below demonstrates how to use Query Store metadata to identify and remove unwanted ad-hoc query plans from the cache. 

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL =

(

        SELECT 'EXEC sp_query_store_remove_query ' 

                + CAST(qsq.query_id AS NVARCHAR(6)) + ';' AS [data()]

        FROM sys.query_store_query_text AS qsqt

        JOIN sys.query_store_query AS qsq

               ON qsq.query_text_id = qsqt.query_text_id

        JOIN sys.query_store_plan AS qsp

               ON qsp.query_id = qsq.query_id

        JOIN sys.query_store_runtime_stats AS qsrs

               ON qsrs.plan_id = qsp.plan_id

        GROUP BY qsq.query_id

        HAVING SUM(qsrs.count_executions) = 1

               AND MAX(qsrs.last_execution_time) < DATEADD (HH, -24, GETUTCDATE())

        ORDER BY qsq.query_id

        FOR XML PATH('')

) ;

EXEC(@SQL) ;

You can find out more about the Query Store in SQL Server 2016, in my new book, Expert Scripting and Automation for SQL Server DBAs, available from Apress at www.apress.com/9781484219423?gtmf=s or at Amazon at www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422?ie=UTF8&keywords=expert%20scripting%20and%20automation&qid=1465300485&ref_=sr_1_1&sr=8-1

Expert Scripting and Automation for SQL Server DBAs

My new book "Expert Scripting and Automation for SQL Server DBAs" is now available to pre-order on Amazon and Apress.com

The link for Amazon.com is www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422/ref=sr_1_1?ie=UTF8&qid=1465205714&sr=8-1&keywords=expert+scripting+and+automation

The link for Amazon.co.uk is www.amazon.co.uk/Expert-Scripting-Automation-Server-DBAs/dp/1484219422/280-0264565-3359429?ie=UTF8&keywords=expert%20scripting%20and%20automation&qid=1465205714&ref_=sr_1_1&sr=8-1

The link for Apress.com is www.apress.com/9781484219423?gtmf=s

It is worth noting that the expected release date on Amazon is not correct. Publication is actually expected in August.

SSIS Toolbox in SSDT for Visual Studio 2015

I am currently writing my latest book - Expert Scripting and Automation for DBAs, which Apress will be publishing later this year. The book is based around SQL Server 2016 and I am currently writing a chapter that is based around SSIS.

I needed to use SSDT for Visual Studio 2015, so that I could use the pre-release version of the Azure Feature Pack, for SSIS 2016. When creating a project, however, I was rather disturbed to find that I had no controls in my Toolbox!

Apparently, there are two Toolbox windows, and SSDT defaults to the wrong one. You should navigate to View | Other Windows and open the SSIS Toolbox window. Normality will at once be restored.


Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom