T-SQL
Column encryption, is it table or data specific?
I ran into an interesting problem lately. I have a table that has encrypted data with a key on a column. The data however needs to be a piece of an ETL process and then it has to become unencrypted.
This led me to ask the question: Can I create a table, insert a record, encrypt the record, move the record to various tables, and finally decrypt the record in a new table successfully?
Boolean order of precedence in T-SQL
I’ve been seeing a lot people get confused with their where clauses lately. Someone wanted to return where (X = A) and (Y = B or Z = C), but what they get is a totally different result. Sure it’s physically written and viewed as (X = A and Y = B or Z = C), but logically it holds a different meaning.
This demo will help illustrate this for you.
Review of Itzik Ben-Gan’s Books
I’ll start by saying this isn’t a paid review nor do I have any association with SolidQ publishing.
T-SQL Fundamentals (third edition) was highly recommended as a read for the 70-761 exam along with his other two books, T-SQL Querying and Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012.
Looking at the three books, you can gather the fundamentals book is probably more base skill set and the exam is structured for obviously an exam. So are all three books worth a read? Which ones should you read and in what order?
Passed the 70-761 exam, Querying with Transact-SQL!
Hurray, I passed 70-761! That means you can too!I just thought I’d share briefly how I passed the exam. As a note, the below is how I studied and what I studied, it may or may not be what was on the exam as they change the exams and I cannot discuss what was on the exam directly. Nor can I answer any specifics to the exam, you can find all of this information on Microsoft’s website. I am going to share however, how I got to the point to where I felt comfortable taking and then passing the exam.
What if I edit a View in use?
There are times we need to modify a view in production, we can use tools like sp_who2 / sp_whoisactive or use the DMV’s and Extended Events ourselves to see who’s using the views at the moment. But what happens when we need to modify the view but it’s currently in use?
Modifying the underlying table for the view should not make an impact when running alter view. This is not true to users trying to access the view.
Auditing your SQL Servers
Below is what I recommend to my clients to evaluate the security of their infrastructure quarterly, although more frequently is always better. Typically with this set of scripts, you’ll want to either compile the data into workbooks, flat files, or at least historic tables so you can track trends and changes.
We begin with two queries; one brings back the SQL Server Name and Instance so we know what we are evaluating and then we check the version of SQL Server which in itself has its own security repercussions based on service pack and year.
Who changed my data?
Odds are that if you are reading this; someone, somewhere, somehow… has changed your data. Odds are also, you may be out of luck figuring out what changed and who did it. The good news is that the lesson has been learned and you can take action now to prevent this in the future.
SQL Server by default does not automatically track who does what to your tables. It does track the made to your database, but it won’t tell you “who done it?”. You can however use the transaction logs with extreme painstaking patience to reveal what changes were made.