SQL Server Propiedades de Base de Datos – Seguimiento de Cambios, Permisos y Propiedades Extendidas

Introducción

Hasta este punto, se han visto las propiedades mas comunes de las bases de datos, archivos que son utilizados, grupos de archivos que son usados y a los que los archivos se asocian, las categorías de opciones que permiten establecer la forma en que la base de datos se comporta. Ahora es el turno de la página de seguimiento de cambios, que se utiliza principalmente para llevar un rastreo de cambios en la información de la base de datos, asimismo se hablara sobre la página de permisos. Cabe mencionar que existe también una pagina denominada Propiedades Extendidas, que permite colocar información sobre la base de datos, por ejemplo, la descripción de la base de datos, alguna liga a un sitio web al que soporta, etc. 

Página de seguimiento de cambios

Esta página es utilizada para ver o modificar la configuración de seguimiento de cambios para la base de datos seleccionada.  Pero, ¿por qué usar el seguimiento de cambios? Esta facilidad se utiliza principalmente para llevar a cabo la extracción de los datos que son utilizados para la actualización de una base de datos que sirve en un almacén de datos. De tal forma, que los procesos de Extracción solo tomaran los datos que han cambiado y no tener que desechar los datos previos y cargar nuevamente toda la información requerida.

Seguimiento de cambios

Esta página puede ser usada para habilitar o deshabilitar el seguimiento de cambios para la base de datos. También puede configurarse el seguimiento de cambios mediante el uso de la instrucción ALTER DATABASE. 

Se muestran las siguientes propiedades asociadas:

Seguimiento de cambios – Indica si la propiedad esta habilitada. El valor Verdadero permite habilitar el seguimiento de cambios en tablas individuales. Cuando esta en Falso, las propiedades a continuación se presentan en solo lectura.

Periodo de retención – Indica el período mínimo para mantener la información de seguimiento de cambios en la base de datos. Los datos se eliminan solo si el valor de Limpieza automática es Verdadero. El valor predeterminado es 2. El período mínimo de retención es de 1 minuto. No hay un período máximo de retención.

Unidades del período de retención - Indica las unidades para el valor del período de retención. Se puede seleccionar entre Días, Horas o Minutos. El valor predeterminado es Días. 

Limpieza automática - Indica sí la información de seguimiento de cambios se elimina automáticamente después del período de retención especificado. 

Pagina Permisos

En esta página se pueden ver y establecer los permisos de los objetos que son protegibles en la base de datos.

La parte superior muestra, en forma de solo lectura

Nombre del servidor – Servidor donde se encuentra el servicio de administración de la base de datos

Nombre de Base de Datos – Indicando el nombre de la base de datos.

En la parte media, aparece una cuadricula con los nombres de Usuario o Roles que utilizan la base de datos, con las siguientes columnas

Nombre – Nombre del usuario o rol

Tipo – indica el tipo asociado

La parte inferior muestra dos pestañas, relacionadas con los permisos asociados con el usuario o rol seleccionado en la parte inmediata anterior. La primera pestaña es la denominada Explicita y la segunda pestaña se denomina Efectiva. En ambas se presentan las siguientes columnas.

Permiso – indica el nombre del permiso

Cedente – Indica el nombre del que otorgante del permiso al usuario o rol

Concedido – Indica si se cuenta con el permiso al usuario o rol

Con Subvención – indica si el permiso puede ser concedido a otros por el usuario o rol.

Negado – Indicara si el permiso está negado al usuario o rol

Página Propiedades Extendidas

Las propiedades extendidas es una característica única de Microsoft SQL Server que permite almacenar información adicional sobre los objetos de la base de datos.

Las propiedades extendidas requieren definir el nombre y el valor asociado a esa propiedad, por ejemplo, supóngase que se  tiene una base de datos que debe seguirse a lo largo de un desarrollo, de tal forma que se debe identificar la version entre valores Desarrollo, Pruebas, Producción. Así, se puede generar una nueva propiedad extendida con esas características.

Conclusión

Hasta este momento, se han visto las propiedades de las bases de datos que son comunes, se han indicado en esta entrega tres tipos de propiedades que son muy dispares. Ya se ha indicado que en estas mismas paginas se pueden establecer los valores requeridos, asimismo, se ha indicado que muchas las propiedades se pueden modificar usando la sentencia ALTER DATABASE. Si se requiere usar T-SQL para obtener la información que se muestra en la pagina indicada, para la categoría varios, es posible usar las siguientes consultas:

/*******************************************************************************

-- Script : Get Database Properties Change-Tracking, Permission, Extended

-- Author : Julio J Bueyes

-- julio.bueyes@outlook.com

--

-- Description : This script helps to get a detailed view of the database properties – Change Tracking, Permission and Extended pages.

--

-- DISCLAIMER. This Code is provided for the purpose of illustration only and is not intended to be used in a production environment. 

--

-- THIS CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, 

-- INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

**********************************************************************************/

-- Set the Database Name required

USE model;

GO

---- Now the information over Change Tracking

SELECT CASE is_db_chaining_on WHEN 1 THEN 'True' ELSE 'False' END AS [Change Tracking],  

    retention_period AS [Retention Period], 

    retention_period_units_desc AS [Retention Period Units],

    CASE ISNULL(is_auto_cleanup_on,0) WHEN 0 THEN '' ELSE  CASE is_auto_cleanup_on WHEN 1 THEN 'True' ELSE 'False' END END AS [Auto Cleanup]

FROM sys.databases DB 

    LEFT OUTER JOIN sys.change_tracking_databases CTD ON DB.database_id = CTD.database_id

WHERE DB.database_id = DB_ID();

--- Get the User or Roles on Database

SELECT name as Name, type_desc AS Type

FROM sys.database_principals dp 

WHERE type = 'R' AND name NOT LIKE 'db_%'

-- Get the Effective Permissions to User or Role on Database

SELECT dp.permission_name AS [Permission], dbp.name AS [Grantor], 

        CASE dp.state WHEN 'G' THEN 1 ELSE 0 END AS [Grant], 

        CASE dp.state WHEN 'W' THEN 1 ELSE 0 END AS [With Grant],

        CASE dp.state WHEN 'D' THEN 1 ELSE 0 END AS [Deny]

FROM sys.database_permissions dp 

    LEFT OUTER JOIN sys.database_principals dbp 

        ON dp.grantor_principal_id = dbp.principal_id AND dbp.type='R'

WHERE dp.class = 0

AND dp.grantee_principal_id = 1

--- Get Extended Properties

SELECT p.name AS [Name],

   p.value AS [Value]

FROM sys.extended_properties AS p

WHERE p.major_id=0 

   AND p.minor_id=0 

   AND p.class=0

ORDER BY [Name] ASC

Como se ha indicado previamente, puede fácilmente obtener los datos directamente de la ventana de propiedades de la base de datos, en las páginas indicadas, no obstante, el script funciona para obtener los mismos valores proporcionados por las páginas.