Small Company Security Example

In a moderately complex security system, multiple people perform various tasks in the database. For example, a database administrator is responsible for the database environment: creating the database, tables, and security accounts, performing backups, and tuning the database. Two developers are responsible for writing client applications to provide an interface to the data. Managers prepare information reports from the database and so need access to all available data. The administrative staff performs customer and sales data entry and must be able to view all data.

The users-to-activity map for this example is slightly more complicated than a single user database.

User account	Activity
LONDON\joetuck	All database access.
LONDON\marysmith, LONDON\billb	Full access to data and the ability to create procedures.
LONDON\managers	Full access to all data.
LONDON\admins	Full access to customer data and sales. Read-only access for all other data.

The first step in installing the security for this example is to add login rights for LONDON\joetuck. Then, because the LONDON\joetuck login requires full access, the next step is to add this user to the sysadmin role.

Login rights should be added for the developers, too. One way to do this is to grant individual developers (LONDON\marysmith and LONDON\billb) permissions to access data. But if another developer (or another 10 developers) joined the project, separate permissions would have to be added to each new person, a time-consuming task. A better solution is to add a SQL Server database role named Developers, granting permissions to access data and creating procedures to the role. When LONDON\marysmith and LONDON\billb, or accounts for other new developers, are added to the Developers role, their user accounts get the permissions granted to the role.

Roles are only applicable at the database level. That is, roles solve the problem of controlling database user access. Instead of individually granting database access to 10 developers, you can create a role, add the 10 developers to it, and grant the role database access.

Finally, login rights must be added to SQL Server for LONDON\managers and LONDON\admins. When a manager connects, she is recognized as a member of an existing Microsoft Windows NT® 4.0 and Windows® 2000 group and allowed to perform activities based on the permissions granted to that group. The same is true for LONDON\admins.