SQL Server Reference Guide

I'm continuing our discussion of SQL Server Security in this series , where I've already described security in general, and on the platform in specific, as well as a graphical step-by-step tutorial on how to create users in the last tutorial. If you're just coming into this series from this tutorial, make sure you check those out as well. They introduce concepts that I'll continue here.

In the earlier tutorials I compared a SQL Server setup to a bank building. In a bank building, security is designed from the outset, and only by following the design is the bank truly secure. I've explained that by choosing the proper options for your server you can secure it similar to the bank building, and by working with users (or as Microsoft calls them, principals), you can treat accounts similar to bank customers.

Working with only a few users is not very difficult. If you have only a few users, normally they have different needs for access into the system. But when you are working with large groups of users, having to set individual rights and privileges becomes more difficult. Many users have similar security needs, and those needs are likely based on the role they fill at an organization, such as "Accountants" or "Managers." In this tutorial I'll explain how to group of users into what Microsoft calls roles in SQL Server 2000 and 2005.

There are two ways to work with groups of users in SQL Server. I'll mention the first method, which isn't controlled directly by SQL Server and then focus primarily on the second method, which is controlled entirely by SQL Server.

The first method of working with groups in SQL Server is to allow the Windows system to handle the groups. This is a common way of handling security if you normally manage everything from the operating system, or if you have a situation where your Windows security is similar to the kinds of SQL Server authentications you want. This authentication works with local Windows groups, domain groups, and Active Directory groups.

In this example, I have several applications hosted on my server, all of which can work with Windows security. I want to handle the security for all of these applications, SQL Server included, by putting users into and out of groups in my local Windows system.

I already have three users on my Windows 2003 server called SQL:

• Jane Manager (SQL\JaneManager)
• Peter Accountant (SQL\PeterAccountant)
• Steve Administrator (SQL\SteveAdministrator)

I want to put these users into two groups: Managers and Staff. I'll use these groups in my SQL Server database as well as other applications I have on my server. Here are the basic steps, whether you're using SQL Server 2000 or 2005:

1. Create Windows groups
2. Assign users to the groups
3. Create Windows Logons in SQL Server, tied to the group
4. Assign the SQL Server Logons to a Database

Let's look at how I handled this for my system. The first two steps work the same way whether you're using Windows 2000 or 2005.

Since I already have my users, I just need to create the Windows groups from step 1. You can do this graphically, either locally or in domains or Active Directory environments. Since I'm using local security, I'll just use the following Windows command-line commands to create the groups:

NET LOCALGROUP Managers /ADD
NET LOCALGROUP Staff /ADD

These are local groups, which means that they are only valid on this server. With the groups created, I'll move on to step 2 and add the users to the groups. I'll put Jane in the Managers group and the other two users in the Staff group, once again from the Windows command line:

NET LOCALGROUP Managers JaneManager /ADD
NET LOCALGROUP Staff SteveAdministrator PeteAccountant /ADD

The users and groups are all ready to go.

Setting up Windows Groups for SQL Server 2000 Authentication

For step 3 I'll open Enterprise Manager and drill down to the server I'm working with. I'll right-click the Security object on the left and then click the New Login menu item that appears:

Figure 240

When the detail dialog box shows, I fill out the information just like I would with a single user, but I enter the name of the Windows group:

Figure 241

Just as I showed you in the last tutorial, you can add the user (in this case the Windows group) to a database from here, but I'll forgo that so that you can see the process from the database side. Instead I close that panel by clicking the OK button, and then I expand the pubs database object on the left. I then right-click the Users object and select New Database User... from the menu that appears:

Figure 242

Once the detail dialog opens I pull down the name of the group, and add it as a user. That's all there is to it. I'll repeat the previous two

Figure 243

I'll repeat the previous two steps for the Staff group as well.

Setting up Windows Groups for SQL Server 2005 Authentication

For step 3 I'll open Management Studio, connect to my server, and in the Object Browser I'll expand the Security item. From there I'll right-click the Logins item and select New Login from the menu that appears:

Figure 244

When the Properties Panel opens I enter the Managers group name as a user:

Figure 245

This time, rather than creating the user (in this case a Windows group) to a database as I did in the last tutorial, I'll assign the database to this Windows group here on this dialog. I'll click the User Mapping item on the left, and click the AdventureWorks database, which adds the group as a user in the database.

Figure 246

Server and Database roles

While the previous method works with some environments, in most shops you'll create and control groups of users using SQL Server roles. There are two types of roles, one at the server level, and another at the database level.

This week, I'll show you how to use roles — SQL Server's version of groups. I'll explain how to make them, where to use them and what the rules are.

You can use SQL Server roles like an operating system’s groups. They are just containers that hold user accounts, and are in turn treated like a user account. You can grant permissions and rights to the roles, and then assign Windows or SQL Server accounts to one or more roles. Using this method you can standardize on your security.

Types of roles

In both SQL Server 2000 and 2005 there are two types of roles — server and database. Server roles are predefined, and allow users to do things like add other users. You can't create more Server roles; you can only add and remove accounts from them. We'll explore this type of role in more detail later in this tutorial.

The other type of role in SQL Server is the Database role. Microsoft delivers several Database roles that cover the rights and privileges that most users need. The difference between Database and Server roles are that you can create more Database roles, and they only allow access to the objects within a specific database.

When you're applying the permissions I’ve explained in the last two tutorials, you can (and normally should) apply them to roles instead. By applying complex permissions to a role instead of an individual account you simplify the amount of work you need to do to maintain your security. When a user leaves the company, there's no need to chase down all their permissions, just remove them from the role.

Naming roles

When you create Database roles, it’s important to consider the names you’ll use. Many administrators create roles using the name of a department or function, such as Marketing or Finance. While using this naming convention does logically group the users, it doesn't explain the security very well within the database. Users don’t often see the names, so it isn't important to use business names. Instead, it makes more sense to name roles based on what they can do, such as Access_Finance_Tables and so forth. If you don't immediately see the logic in this process, then open any SQL Server Enterprise Manager or SQL Server Management Console, navigate to a database, and look at the group names. Can you tell what the groups do?

Predefined roles

Let’s take a look at the server and Database roles that are delivered with SQL Server and what they have rights to do.

SQL Server 2000 Server roles

It's okay to add a login to more than one Server role. Many admins do this so that others can manage the various parts of the server.

With SQL Server 2005, you have the same Server roles:

SQL Server 2005 Server roles

SQL Server 2000 Database roles

Database roles are the type of groups you'll work with most often in SQL Server. Just like the Server roles, there are some pre-defined roles already set up for you:

Once again, the SQL Server 2005 roles are the same.

SQL Server 2005 Database roles

Creating and Using roles

To create roles, you can use graphical tools or commands. For SQL Server 2000, open Enterprise Manager and drill down to the Databases object. Double-click the name of the database you’re interested in adding a role to. Right-click the roles object to create a role. Once you’ve added the role, you can control its rights and add or subtract users by double-clicking it.

In SQL Server 2005, open the SQL Server Management Studio, and then drill down to the Databases item in the Object Browser. Open the database you want to work with, and navigate to Security, then roles and then Database roles. Right-click to add a role. Once you’ve added the role, you can control its rights and add or subtract users by double-clicking it, if you want to work with it graphically.

If you want to create and work with a new role in T-SQL use Query Analyzer (SQL Server 2000) or a new query window in SQL Server Management Studio (SQL Server 2005). This is the preferred method for adding large groups of roles and assigning users to them. It's easier to create these kinds of scripts with multiple lines than clicking around graphically.

To create a new database role, type:

sp_addrole 'rolename' 

Replace rolename with the name you want. The role can't do much at this point, but it's there. To add a user to the role, use the command:

sp_addrolemember 'rolename', 'username'

Notice that it's the database user name, not the server login name. That's only important if you used a different name for the two, which I don't recommend.

To remove a user from a role, type:

sp_droprolemember 'rolename', 'username'

To completely remove the role, use the command:

sp_droprole 'rolename'. 

You can add a logon to more than one Database role. The user gets the greatest allowed permissions, with the exception of any of the "deny" roles. Those supersede any other memberships.

As I mentioned earlier, you should create your roles and then apply the proper level of permissions to them, adding user accounts to the roles. Use this approach even for one account, and grant the rights to that role. When the security plan gets complex, your naming convention will help you wade through it.

Application roles

One of the most useful types of roles for a distributed application is the Application role. An Application role has no users assigned to it, but it does have a password. You grant the rights to the role just as any other.

The difference is that you activate the role using code, such as a SQL Statement, Visual Basic or C# code, passing along the role name and password. The commands are executed, and as soon as the connection is released, the role goes inactive again.

This provides a high level of security. If you create an application using this type of role exclusively, you don't have to create any user accounts. You can also use the role to have an application run a higher-privileged command on behalf of the user.

The T-SQL syntax to create an Application role is:

sp_addapprole 'app_rolename', 'password'

The second parameter is the password you want for the role. Users are not normally given this password. What most developers do is create a table of user information in the database, including passwords the user sets. The developer uses the application role password to check the table to see what the user is allowed to do.

To activate the role, the developer sends the command:

sp_setapprole 'app_rolename', 'password'

As soon as the connection is broken, the role reverts back to an inactive state. You can encrypt the password for the role on the wire with the command:

sp_setapprole 'approlename', {Encrypt N 'password'}, 'odbc'

In the next tutorial, I'll talk about working with these users and roles with the objects they need to access.

Informit Articles and Sample Chapters

Richard Waymire covers more about security in this free chapter from his book Sams Teach Yourself Microsoft SQL Server in 21 Days.

Online Resources

Microsoft’s site for SQL Server Security is here. You can learn all about security on this site as well as download some handy checklists.