917.848.7284Data for Finance and Accounting

Top

SQL

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. Part III: Permissions Now we have the pieces and the people. But how can they do anything? In other words, what can the people (the logins/users) do to the pieces (the databases/schemas and views)? For our purposes, we care about their abilities to add or change data in the database. We can grant that ability in two ways: Through a direct permission In a database, someone has a permission when they can something to something. For example, they can insert (i.e....

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. Part II: The People While a database with no access would be secure, it wouldn’t be all that useful. So how do we tell SQL Server who gets access to the system? We start at the instance level and look at logins: Logins: A login is a way for a person or program to gain access to SQL Server. Let’s analyze a few of the logins I’ve set up here. Single Windows User Login In the above example, NH\ajacobson is a single windows user login. When you see a \ within a domain, you’re seeing a login that first existed in Windows.  Almost everyone reading this post...

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. (It’s disheartening when this happens. You would think that a person working for a large national firm sent to audit a system would have some knowledge of the database. It’s not like we’re using dbase or internally described files on the AS400. Yet, in talking to other folks, this is far from an uncommon problem.) So, to save you the trouble, I’m going to walk through...

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. Why Azure SQL Database Should Work for Me and My Clients In the SQL server universe, I spend most of my time writing queries and doing integration development on SQL Server. I’m not a production DBA. I only learned about the back end to help various clients survive SOX and other financially related orders. So things like backups and redundancy are areas where neither my team nor I have in depth experience. Most of our clients have...

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. We’ll start with an example. Let’s go back to the Order table: You’ll notice the OrderDate column has more than just dates—it also has times. (I find this incredibly aggravating. If you don’t need time, don’t include time! But I digress.) Since the time is always zero, we get the same results whether or not we include time in the WHERE clause. (Advanced users know that running the query without time is less efficient, but that’s a discussion for another day.) Here’s a query without the time: And here’s a query with the time...

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. The Ins and Outs of Aggregates In this post, we’ll focus on the “SUM” aggregate, which is just like SUM in Excel. It delivers the total amount of a column over a number of rows. In the following example, we’ll develop a query to get the total dollar value of orders summarized by product for a given period in time. We start with a basic order detail query: As you can see, SQL Server returns 57 rows. Before we proceed, let me make a few points about this query: Math on columns In the above example,...

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. To illustrate, let's look at two tables: Customers (from the Northwind sample database) and Red3States (which I created). As you can see, it has 91 rows. The region column is returning “NULL” in some rows. And...

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.) Unfortunately, the Northwind sample database (which we’ve been using for this series) doesn’t have an example of this. So for the purposes of this post, I created two additional tables: ship-to address and order header. How to Create a Multi-Column Join I’ve titled the ship-to table, Red3Ship. And I’ve set up two customers, each with two ship-to addresses: Now, let’s look at the Order table. We have one order for each CustomerID/ShipToID combination: First, let’s add the customer name (i.e. CompanyName) to the query: We still have four records. Now, let’s add the ship-to name (i.e. ShipToName): The good news is we have the ship-to...

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. Let’s start with a simple example. We want to add category name to the previous query we built. Here’s the query against the Products table as it currently exists: And here’s the Categories table: If we look at the columns in SSMS, we see that CategoryID is the primary key: And we can see that CategoryID is a foreign key: As mentioned in Part 1 of this series, in most ERP systems, the foreign key is implicit. You can’t always rely on the handy visual cue of these little silver keys. Let’s put the two tables together. (I’m going to intentionally make some mistakes...

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. But first, you need to know these four terms: SELECT specifies the list of columns you want to include in your output. FROM specifies the list of tables you need to access WHERE limits the output based on the values you specify ORDER determines how to sort the output. A Simple Query Let’s start with a very simple query: SELECT * FROM dbo.Products If we enter this query in SSRS, we get the following output: This is the simplest possible query in SQL. But even so it contains several mistakes: We didn’t specify any columns for output We asked for everything, and we should never do this in the...