Uso de Schemas en SQL Server 2005

En SQL Server 2005 cada esquema es un espacio de nombres distinto que existe de forma independientemente del usuario de base de datos que lo creó. Es decir, un esquema simplemente es un contenedor de objetos (vistas, tablas, stored procedures, etc).

Algunas características importantes de los schemas son:

• La propiedad de los esquemas y de los elementos que se pueden proteger (con ámbito de esquema es transferible): ALTER AUTHORIZATION (Transact-SQL).
• Es posible mover objetos entre esquemas: ALTER SCHEMA (Transact-SQL).
• Un mismo esquema puede contener objetos que sean propiedad de varios usuarios de base de datos.
  Varios usuarios de base de datos pueden compartir un mismo esquema predeterminado.
• Se pueden administrar los permisos sobre esquemas y sobre elementos que se pueden proteger con mayor precisión que en las versiones anteriores: GRANT (permisos de esquema de Transact-SQL) y GRANT (permisos de objeto de Transact-SQL).
• Cualquier entidad de seguridad de base de datos puede ser propietaria de un esquema. Esto incluye funciones y funciones de aplicación.
• Es posible eliminar un usuario de base de datos sin necesidad de eliminar objetos en un esquema correspondiente.
• El código escrito para las versiones anteriores de SQL Server puede producir resultados incorrectos si el código considera que los esquemas son equivalentes a los usuarios de base de datos.
• Las vistas de catálogo diseñadas para las versiones anteriores de SQL Server pueden producir resultados incorrectos. Esto incluye a sysobjects.

Realicé un par de pruebas referidas al uso de Schemas en una aplicación multitenant.

1 - Crear el inicio de sesión
2 - Crear el usuario de la base de datos
3 - Crear el schema en la base de datos
4 - Asignar el usuario creado al schema
5 - Asignar permisos a nivel de schema
6 - Las tablas/procedimientos que se agreguen al schema van a tener esos permisos

Notas:
- Los SP y Tablas tienen que estar dentro de cada schema para facilitar la seguridad

Create Table Schema1.Tabla1 ( Texto Varchar(10) )

Insert Into Schema1.Tabla1 Values ( 'Prueba' )

Create Procedure Schema1.usp2

- Si el usuario tiene como default un schema, entonces los SP no se tienen que ejecutar con el nombre de ese schema

Exec usp1 (el Usuario tiene como default Schema1 => el motor de base de datos busca usp1 dentro de ese schema. Si no lo encuentra busca dentro del schema dbo y sino da error)

- Dentro de los SP no hace falta usar el schema de la tabla: Esto permite que el mismo script se pueda recompilar en cada schema si tener que cambiar las intrucciones

Create Procedure Schema1.usp2

As

Select * From Tabla1 --> Accede a Schema1.Tabla1

Create Procedure Schema2.usp2

As

Select * From Tabla1 --> Accede a Schema2.Tabla1