Introducing SQL Server Security

Learn the basics of protecting your enterprise databases

Many Windows 2000 and Windows NT systems administrators must wear multiple hats, one of which might be that of Microsoft SQL Server administrator. Microsoft has done an amazing job of automating many tasks in SQL Server 7.0—to the point where many companies see no need to hire a full-time DBA and instead appoint a Win2K or NT administrator to handle the typical DBA responsibilities. At the same time, companies are increasing the amount of confidential information they store in SQL Server databases. If you're a new DBA, you can probably use some help understanding the SQL Server security model and how to configure security to grant database access to those who need it and to prevent inappropriate database access.

SQL Server 7.0's security model is much improved over earlier versions and is more closely integrated with, and similar to, Win2K and NT security. If you have a SQL Server 6.5 installation and don't have a full-time DBA, I recommend upgrading to SQL Server 7.0 because it's so much easier to administer. Although I used SQL Server 7.0 on NT 4.0 in writing this article, the same instructions and security issues apply to Win2K. For the purposes of this article, SQL Server 2000 has no major differences from SQL Server 7.0, other than some dialog boxes that read "Windows NT/2000" instead of "Windows NT."

Security Levels and Modes
SQL Server has three security levels. First, users must log in to SQL Server or have a valid NT logon that you've mapped to a SQL Server login. Logging in to SQL Server doesn't give users permission to connect to any SQL Server databases. You use the second security level to let users connect to specific databases. The third level lets you assign permissions on objects in a database; for example, you can specify which tables and views a user can see and which stored procedures a group can run. The three security levels have Win2K and NT analogies, so you can extend what you already know about Windows security into SQL Server territory.

SQL Server has two authentication modes: NT security mode and mixed mode. If you choose NT mode and map NT user logons to SQL Server logins, users that NT validates can also connect to SQL Server. In NT mode, users that NT doesn't authenticate can't access SQL Server. In mixed mode, NT users connect to NT and SQL Server as they would under NT mode, and users that NT doesn't validate can supply a name and password to connect to SQL Server. (Alternatively, NT users can use a valid SQL Server login and password, rather than their own NT logon, for SQL Server authentication.) Use NT security mode unless you need mixed mode.

To verify or change the security mode for a SQL Server system, open the Microsoft Management Console (MMC) SQL Server Enterprise Manager snap-in, right-click the server name, and select Properties. Select the Security tab, which Figure 1 shows. If you change the security mode, you must stop and restart SQL Server (you need not reboot the system). In the SQL Server program folder, select Service Manager and use it to stop and restart the SQL Server service and to restart the SQL Server Agent service (stopping the server also stops the agent).

SQL Server Logins and Server Roles
In SQL Server 7.0, you can map an NT group to a SQL Server login. You don't need to add a login for each user. Users who belong to an NT group that has permission to connect to SQL Server can connect without entering a name and password. SQL Server tracks users by their individual NT SIDs rather than by their group SID, so you can determine who made a particular change in SQL Server even if you work with groups rather than individual users. When you add new users to an NT group, the users automatically have access to SQL Server. When you remove users from a group, the users lose their access rights to SQL Server. The catch here is that you must remember that when you add users to NT groups, you might also be giving these users permission to access SQL Server.

Given the possible links between NT groups and SQL Server, the first step in setting up SQL Server security is to plan your NT groups and users strategy. Set up global groups and place users in the groups just as you would if you were assigning NTFS permissions. Then, to add a SQL Server login for a group, open SQL Server Enterprise Manager. If you're logged in as an NT administrator and you have the default security settings for SQL Servers, you can connect to SQL Server as a DBA.

In the left pane of the Enterprise Manager window, expand Microsoft SQL Servers, expand SQL Server Group, expand the entry for the server on which you want to add a login, and expand Security. In a minute, I'll show you how to use the Logins item to assign NT groups and users to SQL Server logins and, for your non-Windows users, how to set usernames and passwords.

Just below Logins is the Server Roles item. Before you start adding logins, click Server Roles and become familiar with the different roles, which Figure 2 shows. These roles are similar to NT's special operator local groups (e.g., server operators, backup operators) in that they're predefined and have certain assigned rights and privileges. You can't add new server roles, and you can't modify those that SQL Server provides. You can also think of server roles as local groups into which you can place Windows global groups.

Double-click a role to open a tabbed dialog box that lets you add new users to the role and see the role's members and permissions. The System Administrators role is equivalent to a super user who can do anything in SQL Server. Reserve this role for those who really need the top level of administrator privilege. You might need to assign developers to the Database Creators role so that they can build test databases. You can make your junior administrators Security Administrators and Server Administrators so that they can administer server properties and security without being full-blown systems administrators.

After you've checked out the server roles, return your attention to logins. The only predefined login is a system administrator login. If you're using mixed-mode security, the first thing you need to do is enter a system administrator password. By default, the password is blank after you install SQL Server. Double-click sa under Logins, and type the new password. When you operate SQL Server in NT security mode, you and your users aren't asked to supply a password, so you don't need to enter one here.

To add a login for an NT group or user, right-click Logins and select New Login to open the SQL Server Login Properties - New Login dialog box, which Figure 3 shows. The first thing you'll notice on the General tab is that you must type a name; no drop-down box offers a list of NT group names or usernames to choose from. (This omission is fixed in SQL Server 2000, but for now, you must type the name.) If you entered an NT group or user, select from the drop-down list the name of the domain containing the group or user. After you select it, the domain name will also appear in the Name field to qualify the group name or username you entered.

Notice that you also use the General tab to grant or deny access to SQL Server. If one person in a group shouldn't have access to SQL Server, you can grant access to the group and deny access to the individual. As in NT, any access denial overrides all the permissions a user has accumulated individually and as a group member.

A SQL Server login gives the group or user permission to connect to SQL Server but not to access any databases. The General tab lets you set a default database for the login, but this setting doesn't grant permission to the database, it just specifies which database SQL Server should connect the group or user to if the group or user has permission to access several databases. Separate tabs on the Login Properties dialog box let you grant permissions to databases and assign groups and users to server roles.

Database Access
The second level of SQL Server security is regulating access to databases. SQL Server can support more than one database on a server, so you'll likely want to give most users access to some databases and not others. A SQL Server login has no rights to a database until you set up the login as a user of that database. You can approach this task from the user side or the database side. You can use the Login Properties dialog box to make a login a user in multiple databases. Or, you can go to the database, open the New Database User dialog box, and add the logins for all the valid users of the database. Figure 4 shows how to use the Login Properties dialog box's Database Access tab to add a user to one or more databases. You can specify a username different from the login, but I don't recommend this practice because it could confuse the administrator.

As you add logins as database users, you can place the logins into database roles, a new concept in SQL Server 7.0. As with server roles, you can think of database roles as being similar to local groups into which you place SQL Server logins, which are similar to Windows global groups. Like a server role, a database role has a set of predefined permissions. You can assign permissions directly to users, but in many cases, simply placing users in the right roles gives them all the permissions they require. A user can be a member of more than one role and accumulates the combined permissions of all the various roles. Any role that denies access overrides all the user's other permissions. As with server roles, you can't modify the predefined database roles. You can add database roles with whatever permissions you choose, but you'll probably be able to combine existing roles to give your users exactly the level of access they need. You can modify role membership at any time; you don't necessarily have to assign a login all its roles when you add it as a database user.

The public database role is similar to Everyone in NT. SQL Server places in the public role any login that you grant database access to. You can't remove users from the public role, nor can you delete it. By default, the public role has no permissions to any database you create. Don't be misled by the example Northwind database in Figure 4—because Northwind is a test database, everyone is supposed to be able to see its data.

You'll place users who need to select data from a database in the db_datareader role. Users who need to modify the data must be in both the db_datareader and db_datawriter roles. If an NT group needs access to a database but an individual in the group shouldn't have that access, you can place the SQL Server login for the group in the db_datareader and db_datawriter roles and place the individual's login in the db_denydatareader and db_denydatawriter roles.

Using the db_datareader and db_datawriter roles has one possible problem. Some databases use views to enforce security. A view is a predefined specification of what the user is allowed to see. For example, a view can be a subset of the data in a table, showing only some columns and hiding other columns that contain confidential data. When you use views to enforce security, you don't give users direct access to database tables. Instead, you assign users specific permissions to the views. You can't use the db_datareader and db_datawriter roles because these roles give users access to all the database tables.

You might want to delegate some of your administrative authority on a database. Two database roles confer limited authority on any members of these roles. A member of the db_accessadmin role can add an existing SQL Server login to the database as a user. A member of the db_securityadmin role can then assign specific permissions on objects such as tables and views to any database user. If you want one person to perform both these tasks, you can add that person's login to both roles.

The db_backupoperator role is conceptually the same as the NT Backup Operator group. Members of this role can read the database only to perform a backup and might have no other access to the data. The db_backupoperator role can back up a database but can't restore it. That's a task for the DBA or the database owner.

If you have a test or development database or programmers are making changes to the production database, the programmers need to be in the db_ddladmin role. Members of this role can create, modify, and remove database objects. Don't worry about the db_owner role. Every object in SQL Server has an owner, and by default, whoever creates a database also owns it.

SQL Server Enterprise Manager doesn't show you which permissions each database role has. For an explanation of the roles, see SQL Server Books Online (BOL). You can access BOL from the SQL Server 7.0 program folder or from many of the Help options in Enterprise Manager. As you'll discover, the predefined roles are flexible, but if they don't meet all your needs, you have a couple of options. One is to assign permissions directly to users. The other is to add your own database roles, give the roles the necessary permissions, then add users to the roles. To add a role from Enterprise Manager, expand the database, right-click Roles, and select New Database Role. In the dialog box, enter a name for the new role, select Standard Role, and add the users you want to be members of this role. Before you can define permissions, you must exit the dialog box to create the role. Then double-click the role and assign the permissions. You can, of course, go back later and change both the membership of the role and the permissions you assigned to it.

Granting Permissions
Whether you want to assign database permissions to users or to roles that you've added, you can perform the task by beginning with the user (remembering that a SQL Server user might correspond to an NT group) or role and assigning the permissions. Or, you can begin with a table, view, or stored procedure and assign permissions to that object to the appropriate users or user-defined database roles.

To assign permissions by user, expand the SQL Server Enterprise Manager hierarchy and select the Users item under the desired database. In the right pane, double-click the name of the desired user to open the Database User Properties dialog box. Click the Permissions button to display the user's permissions for the objects in the database. Figure 5 shows the dialog box. Keep in mind that this interface shows you only the permissions granted specifically to this user. Permissions the user has because he or she is a member of one or more roles or NT groups don't show up here. In fact, obtaining a list of a user's accumulated permissions isn't easy in SQL Server.

To give a user read permission on a table or view, check the SELECT box. If the user needs to modify the data, you typically check the INSERT, UPDATE, and DELETE boxes. However, you might want data-entry staff to be able to insert records but not to change or delete them. Deleting records might be the responsibility of an employee with more authority or experience. To deny a user Delete permission, click the DELETE box twice so that a red X appears, as Figure 5 shows. You might want to discuss user-permission issues with your programmers; if they've built safeguards into the applications that users employ to update the database, you might not need to duplicate the effort. However, building security into the database is safer because then a user can't get to the data by circumventing or modifying applications. The Permissions table shows that user Caesar can select from the Customers table and add or update customer data, but he can't delete a customer. The table also shows that Caesar can't update Employee Territories, but remember that he might belong to a group that has that permission.

The Permissions table's EXEC column regulates the ability to execute stored procedures. Stored procedures are SQL Server-based subroutines written in T-SQL that applications can call to request SQL Server to perform database-related tasks. Programmers use them because they're efficient and offer another level of security. If programmers have written stored procedures, some users will need Execute permission on the procedures to run them.

You can usually ignore the Declarative Referential Integrity (DRI) column unless your programmers tell you otherwise. Sometimes as a user enters data in one table, SQL Server must check data in another table. For example, if a user enters an item number into an orders table, SQL Server might reference a products table to check whether the number entered is a valid item number. For this check to work, the user needs Select permission on the products table and Insert permission on the orders table. In some cases, users might need to reference another table to verify their data entry but shouldn't be able to read the table directly. In these cases, the user needs DRI permission instead of Select permission.

Next Step: Scripts
This overview of SQL Server security should get you started developing your own database security strategy. Your next step might be to generate a SQL Server script. In SQL Server Enterprise Manager, right-click a database, select All Tasks, and select Generate SQL Scripts. This option builds a script that you can use to regenerate the database, including the security policy. Then, learn how to run scripts in the Query Analyzer window. If you need to run only the part of the script that controls the security settings, you can do that. A script can in just a few seconds recreate hours of clicking and selecting in SQL Server Enterprise Manager.