Small Company Security Example
New Information - SQL Server 2000 SP3.
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.
|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 grant login permission to LONDON\joetuck. Then, because the LONDON\joetuck login requires full access, the next step is to add this user to the sysadmin role.
Login permissions must also be added for the developers, LONDON\marysmith and LONDON\billb. Since these developers are not system administrators, do not add them to the sysadmin server role. Instead, grant them access to the database, and then grant object and statement permissions in the database.
However, if you granted these permissions to each developer, the same time-consuming task would have to be performed each time another developer (or another 10 developers) joined the project. A better solution is to add a SQL Server database role named developers, granting object and statement permissions to the role. When developer accounts such as LONDON\marysmith and LONDON\billb are added to the developers role, they automatically obtain the permissions granted to the role.
Finally, login rights must be added to SQL Server for the LONDON\managers and LONDON\admins Windows groups. You then add database user accounts for the groups. You can assign permissions to the group user accounts or you can create database roles, to which you can add the group user accounts. When a member of the LONDON\managers group connects, the member is recognized as a member of the Windows group and is allowed to connect to SQL Server. The login is granted any database permissions granted to the group's user account and any roles to which the group belongs. The same is true for members of the LONDON\admins group.