Microsoft SQL Server 2012 Bible

Object Security

If the user has access to the database, permission to the individual database objects may be granted. Permission may be granted either directly to the user or to a user-defined role and the user assigned to the role. Users may be assigned to multiple roles, so multiple security paths from a user to an object may exist.

User-Defined Database Roles

User-defined database roles, sometimes called user-defined roles, can be created by any user in the server sysadmin, database db_owner, or database security admin role. These roles are similar to those in user groups in Windows. Permissions, and other role memberships, can be assigned to a user-defined database role, and users can then be assigned to the role.

Best Practice

The cleanest SQL Server security plan is to assign object permissions to user-defined database roles and then to assign users to the roles.

Object Permissions

Several specific types of permissions exist:

• Select: The right to select data. Select permission can be applied to specific columns.
• Insert: The right to insert data.
• Update: The right to modify existing data. Update rights for which a WHERE clause is used require select rights as well. Update permission can be set on specific columns.
• Delete: The right to delete existing data.
• DRI (References): The right to create foreign keys with DRI.
• Execute: The right to execute stored procedures or user-defined functions.

Object permissions are assigned with the SQL DCL commands GRANT, REVOKE, and ...