In this example, there are people who perform a few different tasks within the database. The 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. The managers in the company need to view all of the data and prepare information reports. The administrative staff performs data entry and must be able to view customer and sales 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, create procedures |
| LONDON\managers | Full access to all data |
| LONDON\admins | Full access to customer data and sales, read-only for all other data |
The first step in installing the security for this example is to add a user account for LONDON\joetuck. Because the LONDON\joetuck user account requires full access, the next step is to add this user to the sysadmin role.
For the developers, accounts must be added Microsoft® SQL Server™ for LONDON\marysmith and LONDON\billb. Permissions to access the data could be added to both of their user accounts, but if another developer or another ten developers joined the project, the permissions would have to be added to each new person. A better solution is to add a new SQL Server database role named Developers and grant the permissions to access data and create procedures. 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.
Accounts must have to be added to SQL Server for LONDON\managers and LONDON\admins. The permissions should be added directly to SQL Server, so there is no need to add new SQL Server database roles. When a manager connects, they are recognized as a member of an existing Microsoft Windows NT® group and allowed to connect and perform activities based on the permissions granted to their Windows NT group. The same is true for LONDON\admins.