O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required