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.
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 ...