Introducing SQL Server Security

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.

   Previous  [1]  2  Next