Tag Archives: Data

by

All the Masking in the World Can Maybe Cover Your Dirty Laundry….

I have spent the last week learning about new features in SQL Server 2016 and one that I want to play with is Dynamic Data Masking (DDM).

What is data masking? It is a way to change or hide sensitive data. If I want to hide an email address that is Batgirl@DC.com,  I could either change it to be Batwoman@Heros.com using a masking software or I could use DDM to cover it like this BXXXXX@XXXXXX.com. I can also determine how many letters I want to cover with the masking in DDM.

If you want to permanently mask it for security purposes and force it to never link back to your production data, SQL Server Dynamic Data Masking (DDM) is not for you.  The built-in feature only applies a mask over the top, it doesn’t actually change the data that is stored in the database.   Think of SQL Servers’ version of data masking like a Halloween mask that sits on your face as opposed to plastic surgery that will forever change the way you look.

SQL Servers’ DDM will mask data to users that you set up to see the mask.  This is helpful for reporting or for curious people who want to look at data they shouldn’t be viewing.  It will not hide the data from privileged users.  It will not protect your data from someone taking a backup and restoring it somewhere else (If you want that, try Alway Encrypted instead). As a side note, DDM and Alway Encrypted won’t work together on the same column.

Now let’s get ready to play with Dynamic Data Masking in SQL Server.  (Coming next month)

Today’s song is Dirty Laundry by Carrie Underwood.

by

Yeah, that’s my kind of T-SQL!

I have a Dates table and I really love it. This week I needed to do a calculation where I take my monthly budget, divide it by the number of working days in a month and then times it by the working days for the week. That will give me a weekly budget number without having to store it, plus my company only gives me monthly numbers. I had already figured out the daily number and had the calculation working for a 5 day work week when New Years day made everyone notice that it was 5 and I was cheating. So I added a Column to my Dates table to tell me on any given day the number of working days in that week. My weeks run from Sunday to Saturday. I have a Date in my dates table for both the start of the week and the start of the next week. I have a flag (1,0) that says whether or not a day is a weekday and another for whether a day is a holiday. Using this flags, I can pass in a range of dates and get the number of working weekdays.

UPDATE Dates 
SET WorkDaysInWeek = WeekDays 
FROM Dates D 
INNER JOIN  
(SELECT BegW, SUM(IsWeekDay) - SUM(IsHoliday) AS WeekDays 
FROM Dates 
WHERE FullDate Between BegW AND NextWeekStart 
GROUP BY BegW) W ON D.BegW = W.BegW

—————UPDATE—————

But wait, there’s more. Rob Farley who is on twitter @rob_farley sent me an even better way to do it. It gets rid of the need for a WHERE or GROUP BY because we are using OVER. We hoped it would eliminate the sub-query and it does if our query is only a SELECT, but when I go to do an UPDATE, it says that “Windows functions can only appear in the SELECT and ORDER BY clauses”. Rob suggested I use a CTE instead.  I hadn’t ever used a CTE without joining back to it so he taught me how.

This is the super awesome SELECT:

SELECT BegW,SUM(IsWeekday - IsHoliday) OVER (PARTITION BY BegW) AS WeekDays
FROM Dates

Here is what the UPDATE ended up looking like.

WITH d AS (
SELECT *,SUM(IsWeekday - IsHoliday) OVER (PARTITION BY BegW) AS WeekDays
FROM Dates)
UPDATE d SET WorkDaysInWeek = WeekDays;

The other question that Rob had was what if we have holidays on weekends. This is a great question. At my company, the holiday would be shifted to one of the days of the week, so the counts would still be correct. But if you are in a situation where that is not the case you can change the where clause like this:

WHERE [DayOfWeek] NOT IN (1,7)

As I was writing my where clause, I noticed I did something bad and used a reserved word as a column name. If you happen to make this mistake as well, just make sure to put square brackets [] around the word when you use it.

Huge thank you to Rob for being so kind to help me be better! One of my new goals is to play with OVER and understand how to use it and when. I am also going to be learning more about CTE’s and not having to use joins. Yay! New toys!!!!!

by

Would You Let Me See Beneath Your Beautiful SSRS? Query XML from SSRS Content Field in Catalog Table.

Greetings!

I recently needed to find all the reports that access a certain database.  Easy enough, just get all those with that data source, right? Well not exactly.  I have cross database calls happening on some of my reports, some meaning over 200 of them. I found this really cool way to do it and want to save it for the next time that I have to do it.  I hope you enjoy too!

--ItemBin gets the content as a varbinary(max)

WITH ItemBin AS
(
SELECT
ItemID,Name,[Type]
,CASE Type
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription

,CONVERT(varbinary(max),Content) AS Content
FROM ReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8)
),
--ItemNoBOM removes the BOM if it exists...this is some extra junk that you don't need right now.
ItemNoBOM AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content

FROM ItemBin )
--ItemXML gets the content in xml form
,ItemXML AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CONVERT(xml,Content) AS ContentXML
FROM ItemNoBOM
)
--We can then use the XML data type to extract the queries, along with their command and text and place them in a temp table
SELECT
ItemID,Name,[Type],TypeDescription,ContentXML

,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
INTO SSRSXMLTemp
FROM ItemXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemXML.ContentXML.nodes('//*:Query') Queries(Query)

-- Next we join them to the original Catalog table so I can get the Name and Path while also having the command and then filter out what I don't want
--From the command.

SELECT A.NAME, Path, CommandText
FROM SSRSXMLTemp A
INNER JOIN Catalog C ON A.ItemId =C.ItemId
WHERE CommandText LIKE '%Something I am looking for%'
ORDER BY Name