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:
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 uses a Windows login when they come to work each day and starts up their PC.
Generally, the single user (in this example NH\ajacobson) also has login rights to Windows. We know it’s a single user because of the icon which represents one person...
as opposed to the icon for a group, which kind of looks like multiple people (discussed below):
It’s almost always preferable to use this kind of login. With it, you don’t have to worry about passwords expiring within the scope of the accounting system and database because all that’s taken care of by your Windows admin.
Windows Group Login
NH\R3Grp is an example of a Windows group login.
As you can imagine, it wouldn’t be efficient to set up EVERY user as a login to your SQL Server. Generally, (especially for accounting applications), if a single user needs access to the database, many other users in the organization will need similar access. Therefore, Windows allows you to create a group.
So, let’s say you have 20 folks in accounting that will all be running reports over the accounting database. You can have your Windows administrator set up a group (in this case R3Grp) and include all the accounting users in that group. This way you can maintain security for the group and not for the individual. (Of course, your administrator will need to produce a list of everyone in a given group for your auditors.)
SQL Server Login
fransales is an example of a SQL Server login.
When you don’t see the backslash character in a login, the login hasn’t been set up in Windows. That means no one is going to login to their PC as fransales. However, sometimes people will use an application that uses SQL Server, and they’ll be asked for this user name and password.
In general, we want to avoid using these kinds of logins. Using them means maintaining password security in SQL Server. Also, these kinds of logins (and their passwords!) wind up getting hardcoded into spreadsheets. Not that I would ever do something like that.
A Special SQL Server Login
Before I finish with logins, I want to talk about one special login that exists on every SQL Server: sa. Which stands for system administrator.
The sa account can do ANYTHING to EVERYTHING on the server. How to secure this login requires another blog post. But the point is, whatever else happens on your server, if someone has access to this account they can do anything.
Logins alone aren’t enough. You need to “attach” the logins to individual databases. In effect, you have to say not only can this guy use this server, but he can also use this database.
For example, I have a custom database that I want the NH\R3Grp to use. So, I’ve added them as a user to that database:
If we hadn’t added R3Grp to this database, users in this group would have no access to it.
So far, we’ve discussed the pieces of SQL Server and the people with access to SQL Server. In my next post, I’ll talk about permissions—allowing people to do stuff with the pieces.