Remember, your goal is to authenticate users with Win2K or NT 4.0 so that SQL Server can authenticate users with the OS. The authentication method you choose is up to you; just make sure the method meets your overall security needs. For example, certificates work well for some users, but what happens when users need access to your server from many different workstations? You can't rely on just certificates in that case because each workstation won't have all the necessary user certificates.
SQL Server Roles
Now that you know the basics of how SQL Server security can work with Win2K and NT 4.0 security, let's look more closely at a SQL Server security feature that I touched on earlier: roles. After you've set up a Win2K or NT 4.0 user group as a SQL Server login, you can use the login in a couple of ways. You've already seen one way—we assigned the NorthWindReaders login read permission to the Northwind database.
Another way to set up security for a database is to add a SQL Server login for a Win2K or NT 4.0 group as we did earlier, then make the SQL Server login part of a SQL Server role that has the permissions to the database that the group needs. To add an existing login to a role, perform the following steps:
- Open Enterprise Manager.
- Open the Databases folder.
- Open the database to which you want to add the login.
- Select the Roles folder for the database.
- Right-click the role to which you want to add the login, and select Properties.
- Click Add, select the login to add, then click OK.
- Click OK to close the role properties and complete the action.
If you used these steps to add the NorthWindReaders login created earlier to the db_datawriter role, SQL Server would now be able to authenticate users in the NorthWindReaders group and authorize them to read data from and write data to the Northwind database. You could open the properties for the SQL Server login and clear the Northwind database entry on the Database Access page, and users in NorthWindReaders would still have access to Northwind because they're still in the db_datareader and db_datawriter roles for Northwind. However, the NorthWindReaders group doesn't have insert, update, or delete permission because you haven't granted those permissions. You could, of course, create another login and assign it to another role that provides more authority (such as db_owner). Table 1 shows the set of fixed roles that's attached to each SQL Server database.
What if you want better control over security than simply letting a login have access to an entire database? You can edit the role selections you made when you created the login or add your own roles with custom permissions. The roles you create can limit or grant access to specific tables and even to specific columns. To set up your own roles for a database, right-click the Roles folder and select New Database Role. Click Add to add users just as you would with a fixed role, and click Permissions to access the tables, views, stored procedures, and columns that you can set permissions to.
Making SQL Server and IIS work together to use Windows security isn't complex, unless you're trying to go through a proxy server or firewall or use certificates. SQL Server's security is pretty simple to set up because of SQL Server's fixed roles, the ability to create your own roles, and the ability to map roles to Win2K and NT 4.0 user groups. I like user groups because they let me easily set up the permissions for a group of users instead of one user at a time. I can set up a group as the SQL Server login identity, then simply add a user to or remove the user from a group to change the user's authority.
However, you must carefully think through your SQL Server security plan. For instance, if you decide to map Win2K or NT 4.0 groups to SQL Server logins, don't also create SQL Server logins for individuals in the groups. If you restrict users to only Win2K or NT 4.0 group access, you have fewer points of entry to control. I addressed the mechanics of SQL Server's security settings in this article, but security policies and implementation are a separate topic for another time.
End of Article
Prev. page
1
2
[3]
next page -->
