Validating database schema

Rated 3.89/5 based on 595 customer reviews

Unmanaged access by ad hoc queries or poorly tuned queries of the base objects will negatively impact the performance of the application.

Additionally, the use of schemas can help to combine entities from separate applications or functional areas, or logical groups, into a single physical database. It is created by using the CREATE SCHEMA statement.

The object now belongs to a schema – a container that can hold many database objects. This concept creates opportunities to expose database objects within a database for consumption yet protect them from modification, direct access using poor query techniques, or removal by users other than the owner.

The ability to protect database objects in this way has many practical applications.

When a schema is created, it is owned by a principal.

A principal is any entity or object that has access to SQL Server resources.

Schemas are analogous to separate namespaces or containers used to store database objects.Securables include databases and all associated objects. This creates a hierarchy referred to as the securable scope.The following table details the securables, and their scope, in SQL Server: Table 1: SQL Server securable and scope Access to any securable is managed by permissions.Permissions are a set of defined levels of access to a securable and are applied individually or collectively to a securable. Common permissions to be granted are: For a complete list of permissions and the securables to which they can be granted, see SQL Server Books Online.There is also a very handy table-valued function called sys.fn_builtin_permissions that returns either all permissions or securables in a tabular format or for a specific securable.

Leave a Reply