SQL Server Reference Guide

I'm continuing a discussion of SQL Server Security in this series, where I've already described the security in general, and platform Security in specific. In this tutorial I'll explain User security and in the next tutorial I'll explain groups of Users (called roles) in SQL Server. I'll continue to use the analogy of a bank building to explore these security concepts. I'll show you a simple example of setting up a few accounts using the graphical tools in SQL Server.

If you're following along from the previous articles, you've designed your SQL Server with an eye towards security, just like you would when designing the building for a bank, installing and configuring the hardware and software for SQL Server according to best practices. Now you're ready to start adding User accounts into the system. But, as you'll see, Users aren't the only objects that can access SQL Server tables, views and so on. For this reason, Microsoft documentation calls a User a "Principal," since that includes not only User accounts but Roles, and even objects that are neither a Role nor a User. I'll explain those in later articles. For this article, I'll use the word "User" and focus on that type of Principal that accesses SQL Server.

Previously I've explained the concept of "least privilege." This means that you should allow Users to do only what they need to do — no more, and no less. In fact, to be truly secure, you shouldn't add a User to access the server at all if they don't have a specific database task they are allowed to do. In many applications, a program accesses SQL Server on behalf of the end User, and so you'll create very few accounts in SQL Server. If you do create an account and you're in this situation, you should ensure that the User gets no more rights and privileges than they are allowed through the application itself, and notify the application owner that you're creating a direct User account that can access their database. I've seen this done incorrectly too many times, "just to allow the User to extract some data for a report" that completely exposes a database because the developer designed the system for one type of access only. In the case of SQL Server, it's a good practice not to allow anyone into the server that doesn't have a specific need. That's the first gating factor in the process.

Continuing the analogy where the bank building is like SQL Server, then the databases within the Server are similar to accounts within a bank. Just because someone is allowed in the bank doesn't mean that they have access to every account. In fact, they might just be allowed into the bank, but not to any accounts at all.

There are actually two accounts in SQL Server for each User: Server Logins and Database Users. Although they are often thought of as a single unit, they exist in two locations. That means when you create a User, you create them first for the server, and secondly for each database they need to access. I'll explain this process in a few moments.

Another concept that I need cover before you begin adding Users to the system is that there are two types of Users for the server. The first type of User that you can allow into your system is based on Windows Accounts your system has access to. For instance, you can add a User account from an Active Directory Domain if your server belongs to one, or if your server is not a Domain Controller, you can also add accounts from the local Windows system, which is how the examples I'll show you are set up. Adding a Windows account from any of these locations is easy because you don't have to manage account policies or control the password complexity and duration. Whatever password the User sets in Windows is trusted by SQL Server automatically.

During the installation of your server, you made a selection dealing with Security. You selected whether SQL Server would use only Windows accounts, or that it could also use "SQL Server and Windows Authentication Mode" security. If you allowed this mixed security, you're able to add Users not only from Windows, but by adding accounts into SQL Server that Windows doesn't control or even know about. This is useful for accounts that you don't have (and don't want to add) in your Windows domain or Windows local security, such as UNIX accounts.

If you do add accounts into SQL Server directly instead of using Windows accounts, you'll have to manually control the password and account policies. These are weaker in SQL Server 2000 than in 2005 and later. In fact, in SQL Server 2005 and higher you can have SQL Server enforce some of the same policies in the SQL Server accounts as what is set up on the Windows server. This is only true if you're using SQL Server 2005 or later on Windows Server 2003 or later, and it's a slightly more limited set of account policies than in the server, but it is an improvement over SQL Server 2000.

In any case, all of this means that you are able to have up to three accounts with the same name. You could have a "Buck" account in the Active Directory domain that your server belongs to, a "Buck" account in the local Windows system where the SQL Server software is installed, and you could create a "Buck" account in SQL Server. All of these might be different people, and could be granted separate rights and privileges. In fact, each of these Users is abstracted to a number, so they are different, at least to the computer.

I'll explain this using a couple of concrete examples, and I'll use the graphical tools to show you how to add Users to an Instance of SQL Server and one or more databases within that server. In subsequent articles I'll show you a more efficient way of adding the Users with scripts, which is faster and allows you to work with multiple servers and databases quickly.

I have three Users on my Windows Server that require different levels of access to two databases on my Instance of SQL Server. I also need another User that does not belong to my Windows security system. For this example, I'm going to create the accounts on the server and for the two databases. I won't use Roles, just to keep the examples simple. However….

This is always not the best way to add accounts into SQL Server. Normally, you'll create Roles (which I'll cover in the next article), set up the Roles to own objects and have access to other objects, and then place Users in those. I'll show you how in the next article.

There are three Windows accounts that I'll add to my Windows server named "WIN-S5P74QK1KNQ":

Jane Manager (WIN-S5P74QK1KNQ\JaneManager)

 Pete Accountant (WIN-S5P74QK1KNQ\PeteAccountant)

 Steve Administrator (WIN-S5P74QK1KNQ\SteveAdministrator)

On my testing Windows Server, I used these commands to add these accounts to Windows, with a password of P@ssword1 (not very secure, mind you!):

NET USER JaneManager P@ssword1 /ADD

NET USER PeteAccountant P@ssword1 /ADD

NET USER SteveAdministrator P@ssword1 /ADD

You can also do this graphically, or you can add them to your (testing) Active Directory domain — if you use a Domain, then simply replace the name of the Windows Server in these examples with the Domain name from Active Directory.

Later I'll add one more account that isn't on my Windows server, but that still needs access:

Greg Contractor (GregContractor)

I'll start with the Windows accounts. I'll open SQL Server Management Studio (SSMS) and connect to the Instance I have that I've registered with the name WIN-S5P74QK1KNQ. From there, I open the Security and then Logins area with the Object Browser.

After I right-click the Logins item, I select New User... from the menu that appears. That brings up another panel to fill out.

You can click the Search button here to type in part or all of the User name to find them quickly, or simply type the name of the Domain (in my case the name of my Windows Server, WIN-S5P74QK1KNQ) a back-slash, and the name of the User.

I'll leave the type of User set to Windows authentication, but notice at the bottom of this panel I've set Jane to have a Default database. This is used if Jane (or an application that is passing Jane's credentials along) logs in to SQL Server without specifying which database she would like to execute commands on. It's far too common to leave this set to master, which then becomes polluted with tables and other objects created by a User when they forget to change databases at the command-line.

I'm going to ignore the Server Roles tab for this tutorial, because I'll show it in the next one. I'll click the User Mapping tab.

Here you can see that I've mapped Jane to two databases — although she doesn't have the ability to do anything in them just yet.  I'll cover that in more depth when I explain Securables in a couple of tutorials from now. For now, I'll leave her in the public Role, which by default allows her only access to the database itself, but not to any objects inside it.

The nice thing about this approach is that you can create a Server Login and a Database User Account all at once. You could even map the User to Roles as shown on this panel, or you could select the Securables tab and even map the User to specific database objects.

But why do you have to add the User to two locations to begin with? The answer to that has to do with how SQL Server handles security accounts. Accounts are stored (unsurprisingly) in a table within a database. The database that controls SQL Server after startup is master. There's a table within master called syslogins that holds all of the accounts (Principals) that are allowed to access SQL Server, and holds keys to other tables that determines whether they allowed to perform any operations on tables or other objects, and whether they are part of any Roles at the server level that are allowed to do things like take backups and so on. Each database also has a table for security, called sysusers, which holds the same kind of information for that particular database. Each Principal, including a User, is tied back to the master database by a key. Joining all this up is what determines which databases a User has access to, and then which objects within those databases the User can access and to what extent.

You shouldn't work with these tables directly. You should always either use the CREATE statements I'll show you later to create a new Server Login and Database User or use various Stored Procedures to make these changes. The graphical tools I'm showing you here use these commands to make the changes correctly as well.

If you're interested in learning more about these commands, I'll detail them for you in a few articles. For now, you can either click the OK button on this panel to add the User or you can click the Script button at the top left of the panel and SSMS will write the code for you. You can copy that code to the clipboard, open it in another Query window, or even save it off to a file. Again, the best way to work with security is to use the scripts I'll show you later, because it's easy to transport the commands around to work with other servers, like between development, test and production.

I'll add the other Users the same way, but recall that one of them is not using my Windows Server. For GregContractor, I'll create a SQL Server account. I follow the same process to open that first panel, but this time I have more to fill out.

In this screen you can see that I've added Greg as a SQL Server User account, because my SQL Server Instance is set up to allow both Windows and SQL Server authentication — sometimes called "mixed authentication." In this case, Greg's account isn't present in Windows at all, but the application he runs will prompt him to enter the information I've filled out here. I've given him a name, a password and a default database.

Notice that I selected that SQL Server should enforce the password policy as defined on Windows for this account. While it's only a subset of what Windows can require, it's a good place to start. I've also set that passwords for Greg should expire when the Windows Server passwords do. While that's a great thing to do, I have to let the developer know that I've done this, because Greg will use a program to access SQL Server, not SSMS or other tools. The code has to trap a failed login error and also allow Greg to set a new password from the application itself. That's why I de-selected the next option, because the developer did not create code to trap the immediate "change your password" error.

You've now seen the simplest way to add a User account to SQL Server, and in the next few tutorials I'll explain the powerful concept of Roles, and then how to work with each of these using the command line.