SQL Server - Cómo identificar y cambiar el schema predeterminado de los usuarios de la base de datos

Faaala chicos!!
En este post me gustaría abordar un tema muy común en el día a día de un DBA, que es el esquema predeterminado de los usuarios en la base de datos, que puede o no ser diferente del predeterminado (dbo), tal como lo define TI.

¿Qué es el esquema en SQL Server? ¿Para qué es?

Haga clic aquí para ver el contenido de este tema.

Antes de continuar con el tema principal de este artículo, haré un breve explicación de qué es el objeto de esquema en SQL Server y algunas formas de beneficiarse del uso de este tipo de objeto.

Un esquema es básicamente una colección de objetos que tiene como objetivo agrupar varios objetos de un departamento, área o propósito, ya sea por motivos de organización de tablas, Procedimientos Almacenados, etc., o por motivos de control de acceso y seguridad.

organización de mesas

Uno de los propósitos de utilizar un esquema es organizar mejor los objetos en la base de datos. Cuando todos sus objetos están en el mismo esquema, solo puede haber una tabla "Direcciones", por ejemplo. Si intenta crear una nueva tabla con el mismo nombre, SQL Server le alertará de que ya existe una tabla con ese nombre e interrumpirá su comando.

Cuando usamos esquemas para organizar objetos, podemos tener una tabla "Direcciones" en el esquema del cliente, que son las direcciones de los clientes, y podemos tener la tabla "Direcciones" en el esquema Proveedores, que son las direcciones de los proveedores de la empresa. Este tipo de organización de los objetos de la base de datos hace que el propósito de tablas, procedimientos, funciones, etc., sea mucho más intuitivo.

Una forma muy clásica de organizar objetos es en proyectos de BI. Es bastante común que los arquitectos de BI utilicen dim (Dimension), facto (Fact Tables), stg (Staging) y otros esquemas para organizar las capas y propósitos de cada tabla, donde, por ejemplo, la tabla stg.Clientes es la tabla “Clientes” que se importó en origen y se cargó en la capa Staging y la tabla dim.Clientes ya es la dimensión del cliente, una tabla ya tratada y preparada para ser utilizada en proyectos de BI.

Control de acceso y seguridad

Otro propósito muy importante del uso de esquemas es cuando hablamos de control de acceso y seguridad. Como sabrás, en SQL Server podemos liberar permisos en tablas individuales (Ej: GRANT SELECT ON dbo.Table TO usuario) y también a nivel global en la base de datos (Ej: GRANT SELECT TO usuario), donde tendrá acceso a todos los objetos de esa base de datos.

Este tipo de enfoque termina dificultando el control de acceso en entornos con cientos o miles de objetos cuando todos están en el mismo esquema, ya que hay que analizar cada objeto individualmente o otorgarles permiso a todos.

Una forma de mejorar el control sobre los permisos de objetos es mediante el uso de esquemas, que actúan como un nivel intermedio entre el acceso individual y el acceso global, donde puede liberar el permiso SELECT solo en el esquema del cliente, por ejemplo. El usuario que recibió este permiso podrá consultar todas las tablas del esquema del cliente, pero sólo estos objetos (a menos que tenga otros permisos).

Cuando agrupas tus objetos usando un esquema por departamento, propósito o sistema, por ejemplo, el control termina siendo mucho más simple que tener que controlar individualmente cada tabla o Procedimiento Almacenado.

Mesas “privadas”

Otro propósito del uso de esquemas es crear tablas "privadas", donde las tablas se crean, de forma predeterminada, en un esquema igual al nombre de usuario cuando se usa autenticación AD y un propietario predeterminado indefinido.

Esto significa que el usuario puede crear tablas de prueba con el nombre que quiera, ya que aunque la tabla [dbo].Clientes existe, la tabla [DOMINIO\dirceu.resende].Clientes no. Además, es fácil identificar quién es el propietario de determinadas tablas "temporales" creadas en la base de datos.

Todo ello sin impactar al resto de usuarios ni a la aplicación.

Preguntas frecuentes: preguntas básicas sobre esquemas

¿Dónde están mis esquemas de base de datos?

Visualización de esquemas de bases de datos de ejemplo

¿Cómo se organizan las tablas en los esquemas?

¿Cómo consulto datos de tablas con esquemas personalizados?

¿Cómo creo un nuevo esquema?

¿Cómo cambio el esquema predeterminado de un usuario?

Cómo identificar usuarios con un esquema predeterminado igual al nombre de usuario

En el día a día de un DBA, es común encontrar entornos donde el esquema predeterminado de algunos usuarios con autenticación de Windows no estaba definido en el momento de su creación, asignando así un nuevo esquema igual al nombre de ese usuario. Como resultado, los usuarios que no tienen mucho conocimiento de bases de datos terminan creando varios objetos privados en el entorno (sin saberlo) y quejándose ante el DBA de que otros procesos y usuarios no ven las tablas que crean.

Para ayudarlo con esta misión, le proporcionaré un script simple para identificar qué esquema tiene cada usuario y quién es el propietario de estos esquemas.

SELECT 
    A.[name] AS username,
    A.type,
    A.[default_schema_name] AS [default_schema],
    C.[name] AS [schema_owner]
FROM
    sys.database_principals A
    LEFT JOIN sys.schemas B ON A.default_schema_name = B.[name]
    LEFT JOIN sys.database_principals C ON B.principal_id = C.principal_id
WHERE
    A.[type] IN ('U', 'S', 'G')
    AND A.principal_id > 4
ORDER BY
    1

Resultado:

Y ahora, cómo identificar usuarios con un esquema predeterminado distinto al predeterminado (dbo):

SELECT 
    A.[name] AS username,
    A.type,
    A.[default_schema_name] AS [default_schema],
    C.[name] AS [schema_owner],
    'ALTER USER [' + A.[name] + '] WITH DEFAULT_SCHEMA=[dbo]' AS change_schema,
    'ALTER USER [' + A.[name] + '] WITH DEFAULT_SCHEMA=[' + A.[default_schema_name] + ']' AS current_schema
FROM
    sys.database_principals A
    LEFT JOIN sys.schemas B ON A.default_schema_name = B.[name]
    LEFT JOIN sys.database_principals C ON B.principal_id = C.principal_id
WHERE
    A.[type] IN ('U', 'S', 'G')
    AND A.principal_id > 4
    AND (B.[schema_id] IS NULL OR B.[name] NOT IN ('dbo', 'guest'))
ORDER BY
    1

Resultado:

Cómo identificar a los usuarios que poseen un esquema

Otra tarea muy común es identificar esquemas que son propiedades de determinados usuarios. Este escenario evita que los usuarios sean eliminados, con el siguiente mensaje de error:

Mensaje 15138, Nivel 16, Estado 1, Línea 41
La base de datos principal posee un esquema en la base de datos y no se puede eliminar.

Para poder eliminar este usuario, primero deberá eliminar los esquemas de los que es propietario o migrar la propiedad de estos esquemas a otro usuario. El siguiente script le ayudará a identificar cuáles son estos esquemas:

SELECT 
    B.[name] AS [schema],
    A.[name] AS [schema_owner],
    'DROP SCHEMA [' + B.[name] + '];' AS drop_schema,
    'CREATE SCHEMA [' + B.[name] + ']; ALTER AUTHORIZATION ON SCHEMA::[' + B.[name] + '] TO [' + A.[name] + '];' AS create_schema
FROM
    sys.database_principals A
    JOIN sys.schemas B ON A.principal_id = B.principal_id
WHERE
    A.[type] IN ('U', 'S', 'G')
    AND A.principal_id > 4
ORDER BY
    2

Resultado:

Cómo cambiar el esquema predeterminado en todas las bases de datos

Para aplicar el script anterior a todas las bases de datos, usemos el conocido sp_MSforeachdb:

IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados
CREATE TABLE #Dados
(
    [database] NVARCHAR(128),
    [username] NVARCHAR(128),
    [type] CHAR(1),
    [default_schema] NVARCHAR(128),
    [schema_owner] NVARCHAR(128),
    [change_schema] NVARCHAR(MAX),
    [current_schema] NVARCHAR(MAX)
)

INSERT INTO #Dados
EXEC master.dbo.sp_MSforeachdb '

IF (''?'' NOT IN (''master'', ''msdb'', ''model'', ''tempdb''))
BEGIN

    SELECT 
        ''?'' AS [database],
        A.[name] AS username,
        A.type,
        A.[default_schema_name] AS [default_schema],
        C.[name] AS [schema_owner],
        ''USE [?]; ALTER USER ['' + A.[name] + ''] WITH DEFAULT_SCHEMA=[dbo]'' AS change_schema,
        ''USE [?]; ALTER USER ['' + A.[name] + ''] WITH DEFAULT_SCHEMA=['' + A.[default_schema_name] + '']'' AS current_schema
    FROM
        [?].sys.database_principals A
        LEFT JOIN [?].sys.schemas B ON A.default_schema_name = B.[name]
        LEFT JOIN [?].sys.database_principals C ON B.principal_id = C.principal_id
    WHERE
        A.[type] IN (''U'', ''S'', ''G'')
        AND A.principal_id > 4
        AND (B.[schema_id] IS NULL OR B.[name] NOT IN (''dbo'', ''guest''))

END'


SELECT * FROM #Dados

Resultado:

Cómo identificar objetos creados en esquemas distintos al predeterminado

Otra tarea común en la rutina diaria de un DBA es identificar objetos que se crearon en esquemas de usuario y moverlos al esquema dbo (o cualquier otro esquema), cuando sea necesario. Para hacer esto, proporcionaré el siguiente script:

SELECT
    A.[name] AS [object],
    A.[type_desc],
    A.create_date,
    A.modify_date,
    A.[default_schema_name] AS [schema_name],
    C.[type_desc] AS [user_type],
    'ALTER SCHEMA [dbo] TRANSFER [' + B.[name] + '].[' + A.[name] + '];' AS [to_default_schema]
FROM
    sys.objects A
    JOIN sys.schemas B ON B.[schema_id] = A.[schema_id]
    JOIN sys.database_principals C ON B.[name] = C.[name]
WHERE
    C.[type] IN (''U'', ''S'', ''G'')
    AND C.principal_id > 4
    AND C.is_fixed_role = 0
ORDER BY
    1

Resultado:

¿Qué pasa si quiero mover objetos de un esquema a otro?

DECLARE
    @schema_origem VARCHAR(128) = 'dirceu.resende',
    @schema_destino VARCHAR(128) = 'clientes'

SELECT 
    A.[name] AS [object],
    A.[type_desc],
    A.create_date,
    A.modify_date,
    B.[name] AS [schema_name],
    C.[type_desc] AS [user_type],
    'ALTER SCHEMA [' + @schema_destino + '] TRANSFER [' + B.[name] + '].[' + A.[name] + '];' AS [to_default_schema]
FROM
    sys.objects A
    JOIN sys.schemas B ON B.[schema_id] = A.[schema_id]
    JOIN sys.database_principals C ON B.[name] = C.[name]
WHERE
    B.[name] = @schema_origem
ORDER BY
    1

Resultado:

Procedimiento almacenado para unificar esquemas y objetos estándar

Si está buscando una solución muy práctica para restablecer el esquema predeterminado de todos los usuarios al predeterminado (dbo) y mover también todos los objetos privados al esquema dbo, utilice el procedimiento almacenado a continuación:

IF (OBJECT_ID('dbo.stpRedefine_Schema_Padrao') IS NULL) EXEC('CREATE PROCEDURE dbo.stpRedefine_Schema_Padrao AS SELECT 1')
GO

ALTER PROCEDURE dbo.stpRedefine_Schema_Padrao (
    @Fl_Altera_Usuarios BIT = 1,
    @Fl_Altera_Objetos BIT = 1,
    @Fl_Debug BIT = 0
)
AS
BEGIN

    
    SET NOCOUNT ON


    DECLARE @Comando VARCHAR(MAX)


    IF (@Fl_Altera_Usuarios = 1)
    BEGIN


        IF (OBJECT_ID('tempdb..#Usuarios') IS NOT NULL) DROP TABLE #Usuarios
        CREATE TABLE #Usuarios
        (
            [database] NVARCHAR(128),
            [username] NVARCHAR(128),
            [type] CHAR(1),
            [default_schema] NVARCHAR(128),
            [schema_owner] NVARCHAR(128),
            [change_schema] NVARCHAR(MAX),
            [current_schema] NVARCHAR(MAX)
        )

        INSERT INTO #Usuarios
        EXEC master.dbo.sp_MSforeachdb '

        IF (''?'' NOT IN (''master'', ''msdb'', ''model'', ''tempdb''))
        BEGIN

            SELECT 
                ''?'' AS [database],
                A.[name] AS username,
                A.type,
                A.[default_schema_name] AS [default_schema],
                C.[name] AS [schema_owner],
                ''USE [?]; ALTER USER ['' + A.[name] + ''] WITH DEFAULT_SCHEMA=[dbo];'' AS change_schema,
                ''USE [?]; ALTER USER ['' + A.[name] + ''] WITH DEFAULT_SCHEMA=['' + A.[default_schema_name] + ''];'' AS current_schema
            FROM
                [?].sys.database_principals A
                LEFT JOIN [?].sys.schemas B ON A.default_schema_name = B.[name]
                LEFT JOIN [?].sys.database_principals C ON B.principal_id = C.principal_id
            WHERE
                A.[type] IN (''U'', ''S'', ''G'')
                AND A.principal_id > 4
                AND (B.[schema_id] IS NULL OR B.[name] NOT IN (''dbo'', ''guest''))

        END'


        SET @Comando = ''

        SELECT @Comando += change_schema + CHAR(10)
        FROM #Usuarios
        ORDER BY [database], username


        IF (@Fl_Debug = 1)
        BEGIN
            PRINT '------------------- Alterando o schema padrão dos usuários -------------------'
            PRINT ''
            PRINT @Comando
        END
        ELSE
            EXEC(@Comando)


    END



    IF (@Fl_Altera_Objetos = 1)
    BEGIN

    
        IF (OBJECT_ID('tempdb..#Objetos') IS NOT NULL) DROP TABLE #Objetos
        CREATE TABLE #Objetos
        (
            [database] NVARCHAR(128),
            [object] NVARCHAR(128),
            [type] VARCHAR(30),
            [schema_name] NVARCHAR(128),
            [schema_owner] NVARCHAR(128),
            [change_schema] NVARCHAR(MAX)
        )


        INSERT INTO #Objetos
        EXEC master.sys.sp_MSforeachdb '
        IF (''?'' NOT IN (''master'', ''msdb'', ''model'', ''tempdb''))
        BEGIN

            SELECT 
                ''?'' AS [database],
                A.[name] AS [object],
                A.[type_desc],
                A.[default_schema_name] AS [schema_name],
                C.[type_desc] AS [user_type],
                ''USE [?]; ALTER SCHEMA [dbo] TRANSFER ['' + B.[name] + ''].['' + A.[name] + ''];'' AS [to_default_schema]
            FROM
                [?].sys.objects A
                JOIN [?].sys.schemas B ON B.[schema_id] = A.[schema_id]
                JOIN [?].sys.database_principals C ON B.[name] = C.[name]
            WHERE
                C.[type] IN (''U'', ''S'', ''G'')
                AND C.principal_id > 4
                AND C.is_fixed_role = 0
        
        END'


        SET @Comando = ''

        SELECT @Comando += change_schema + CHAR(10)
        FROM #Objetos
        ORDER BY [database], [object]


        IF (@Fl_Debug = 1)
        BEGIN
            PRINT '------------------- Alterando o schema padrão dos objetos -------------------'
            PRINT ''
            PRINT @Comando
        END
        ELSE
            EXEC(@Comando)


    END


END

Ejemplo de uso:

EXEC dbo.stpRedefine_Schema_Padrao
    @Fl_Altera_Usuarios = 1, -- bit
    @Fl_Altera_Objetos = 1, -- bit
    @Fl_Debug = 1 -- bit

Resultado:

Referencias
– https://pt.stackoverflow.com/questions/21031/o-que-s%C3%A3o-schemas-quais-as-vantagens-de-us%C3%A1-lo
– https://www.devmedia.com.br/schemas-no-sql-server/24328
– http://www.informit.com/articles/article.aspx?p=1216889&seqNum=2
– https://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-schemas-in-sql-server/
– https://www.quackit.com/sql_server/sql_server_2008/tutorial/sql_server_database_schemas.cfm

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…