Microsoft SQL Server 2012 Bible

A Sample Security Model

The simplest way to assign permissions when more granularity is needed is to create user-defined roles and select effective permissions. Tables 33.2 and 33.3 list sample user-defined roles and user permission settings of the user-defined database roles. Table 33.3 lists a few of the users and their roles.

Table 33.2 Sample User-Defined Role Permission Assignment

Table 33.3 Sample User and Windows Group Role Assignments

User / Windows Group	Database Roles
Sam	Admin
John	Public
Larry	DBTeam
Clerks (Betty, Tom, Martha, and Mary)	DataEntry

From this security model, the following users can perform the following tasks:

• Betty, as a member of the Clerks Windows group assigned to the DataEntry user-defined role, can execute the application that executes stored procedures to retrieve and update data. Betty can run select queries as a member of the Public role.
• Larry, as part of the DBTeam user-defined role, can perform any task in the database as a member of the sysadmin server role.
• John cannot connect, read, or write data to any database by default as a member of the public role.
• As a member of the Admin role, Sam can execute all stored procedures. He can also manually modify any table using queries.
• Only Larry has unrestricted access to the database server, which includes the ability to modify server and database roles and permission assignments.