Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
Microsoft SQL Server Administration
- The DBA Survival Guide: The 10 Minute SQL Server Overview
- Preparing (or Tuning) a Windows System for SQL Server, Part 1
- Preparing (or Tuning) a Windows System for SQL Server, Part 2
- Installing SQL Server
- Upgrading SQL Server
- SQL Server 2000 Management Tools
- SQL Server 2005 Management Tools
- SQL Server 2008 Management Tools
- SQL Azure Tools
- Automating Tasks with SQL Server Agent
- Run Operating System Commands in SQL Agent using PowerShell
- Automating Tasks Without SQL Server Agent
- Storage – SQL Server I/O
- Service Packs, Hotfixes and Cumulative Upgrades
- Tracking SQL Server Information with Error and Event Logs
- Change Management
- SQL Server Metadata, Part One
- SQL Server Meta-Data, Part Two
- Monitoring - SQL Server 2005 Dynamic Views and Functions
- Monitoring - Performance Monitor
- Unattended Performance Monitoring for SQL Server
- Monitoring - User-Defined Performance Counters
- Monitoring: SQL Server Activity Monitor
- SQL Server Instances
- DBCC Commands
- SQL Server and Mail
- Database Maintenance Checklist
- The Maintenance Wizard: SQL Server 2000 and Earlier
- The Maintenance Wizard: SQL Server 2005 (SP2) and Later
- The Web Assistant Wizard
- Creating Web Pages from SQL Server
- SQL Server Security
- Securing the SQL Server Platform, Part 1
- Securing the SQL Server Platform, Part 2
- SQL Server Security: Users and other Principals
- SQL Server Security – Roles
- SQL Server Security: Objects (Securables)
- Security: Using the Command Line
- SQL Server Security - Encrypting Connections
- SQL Server Security: Encrypting Data
- SQL Server Security Audit
- High Availability - SQL Server Clustering
- SQL Server Configuration, Part 1
- SQL Server Configuration, Part 2
- Database Configuration Options
- 32- vs 64-bit Computing for SQL Server
- SQL Server and Memory
- Performance Tuning: Introduction to Indexes
- Statistical Indexes
- Backup and Recovery
- Backup and Recovery Examples, Part One
- Backup and Recovery Examples, Part Two: Transferring Databases to Another System (Even Without Backups)
- SQL Profiler - Reverse Engineering An Application
- SQL Trace
- SQL Server Alerts
- Files and Filegroups
- Full-Text Indexes
- Read-Only Data
- SQL Server Locks
- Monitoring Locking and Deadlocking
- Controlling Locks in SQL Server
- SQL Server Policy-Based Management, Part One
- SQL Server Policy-Based Management, Part Two
- SQL Server Policy-Based Management, Part Three
- Microsoft SQL Server Programming
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
SQL Server Security: Users and other Principals
Last updated Mar 28, 2003.
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.