917.848.7284 Data for Finance and Accounting

Financial Reporting and Accounting Blog

Explaining SQL Server Security, Part 3: Permissions

Adam Jacobson July 25,2017

In an earlier couple of posts, I had described three critical elements of SQL Server security: pieces, people and permissions.

For a non-technical person (such as, perhaps, an auditor) to understand SQL Server security, they need to have a grasp of all three elements.

You can read more about the four pieces of SQL Server as well as the different types of people (i.e. logins).

In this post, I’m going to cover the third essential element: Permissions.

Read More

Tags: SQL

Explaining SQL Server Security, Part 2: People

Adam Jacobson July 20,2017

In an earlier post, I explained three essential elements of SQL Server security: pieces, people and permissions. Your auditor (or any other non-technical person) will need to have an understanding of all three parts to understand how security works in SQL Server.

In this post, I’m going to explain the second element: People.

Read More

Tags: SQL

Explaining SQL Server Security, Part 1: Pieces

Adam Jacobson July 18,2017

I get calls from clients to answer auditor questions on a somewhat regular basis. These questions usually start with the accounting software and then drill down to the database level. Then, I often get this entirely reasonable question: Who can change data on SQL Server?

This is, as I said, an entirely reasonable question. Unfortunately, sometimes the person asking the question knows next to nothing about SQL Server. Which makes it difficult to answer this question without launching into a lengthy explanation of SQL Server Security 101.

Read More

Tags: SQL

Azure SQL Database vs. SQL Server on an Azure Virtual Machine

Adam Jacobson April 18,2017

I’m working on my first paid project using Azure. Previously, I’d set up test databases in Azure but nothing focuses the mind like having to get something working for a real client. I am hardly an Azure expert, but most of my clients are interested in or using some sort of cloud application, of which Azure SQL database is one. So, part of my motivation for writing this post is to get my thoughts clear.

Read More

Tags: SQL

SQL for Super Users Part 7: Date vs. Date-Time

Adam Jacobson May 11,2016

If you haven’t read all the other parts in this series, you’ll want to start with the introductory post.

I was initially unsure whether to include the topic of dates and date times in an introductory series. It tends to confuse the heck out of people.

But given how often mistakes are made, it’s an important topic. So let’s give it a go.

Read More

Tags: SQL

SQL for Super Users Part 6: Aggregates and Having vs. Where

Adam Jacobson May 10,2016

New to this series? Start with our intro post.

So far in this series, every query we’ve built has returned details, i.e. one row of output for every row selected in the database.

But sometimes, we want totals only, not details. Or maybe we want the biggest (maximum) or smallest (minimum) value of a column from the rows selected. These are called aggregates.

Read More

Tags: SQL

SQL for Super Users Part 5: The Left Outer Join

Adam Jacobson May 9,2016

If you’re new to this series, you should start by reading our introductory post.

In the previous posts in this series (on single table joins and multi-column joins) we had matching records in our two tables.

But sometimes, you may not have data in your second table. This kind of thing can come up when (for example):

  • All vendors don't have a 1099 code
  • All employees aren’t assigned to a department
  • All GL accounts don't have a sub account or cost center.

And this is where a left outer join comes in handy. We use it when we may or may not have a record in the second table, but we want all the data from the primary table.

Read More

Tags: SQL

SQL for Super Users Part 4: The Multi-Column Join

Adam Jacobson May 6,2016

To read this series from the beginning, start with our intro post.

The previous post in this series gave an example of the simplest possible join—a join on a single column. But life isn’t always that easy. In real-world Dynamics tables, the key often includes multiple columns. (For example, when a customer has multiple ship-to addresses.)

Read More

Tags: SQL

SQL for Super Users Part 3: Adding Another Table Using JOINs

Adam Jacobson May 5,2016

Querying data from a single table doesn’t help much in the real world. We need to be able to query more than one. And to do that we have to use joins.

Joins are a big topic. Even without going into too much detail, I’m going to need three posts to give an overview.

Read More

Tags: SQL

SQL for Super Users Part 2: SELECT Statements

Adam Jacobson May 4,2016

If you’re reading this series for the first time, you’ll want to start with our introductory post. It explains everything you need to know about this series.

In Part 1 of this series, we covered some basic terminology.

In today’s post, we move on to create a couple simple queries using SELECT statements.

Read More

Tags: SQL