Faaala pessoal!!
Nesse post eu gostaria de abordar um assunto muito comum no dia a dia do DBA, que é o schema padrão dos usuários no banco de dados, que pode ser ou não, diferente do padrão (dbo), conforme definição da TI.

O que é schema no SQL Server? Para que serve?

Clique aqui para visualizar o conteúdo desse tópico
Antes de prosseguir com o tema principal desse artigo, vou fazer uma breve explicação sobre o que é o objeto schema no SQL Server e algumas formas de se beneficiar do uso desse tipo de objeto.

Schema é basicamente uma coleção de objetos que tem o intuito de agrupar vários objetos de um departamento, área ou finalidade, seja por questões de organização de tabelas, Stored Procedures, etc, ou por motivos de controle de acesso e segurança.

Organização de tabelas

Uma das finalidades de se utilizar um schema, é organizar melhor os objetos no banco de dados. Quando todos os seus objetos estão no mesmo schema, só pode existir uma tabela “Enderecos”, por exemplo. Caso você tente criar uma nova tabela com o mesmo nome, o SQL Server irá te alertar que já existe um tabela com esse nome e interromper o seu comando.

Quando utilizamos schemas para organizar os objetos, podemos ter uma tabela “Enderecos” no schema cliente, que são os endereços dos clientes e podemos ter a tabela “Enderecos” no schema Fornecedores, que são os endereços dos fornecedores da empresa. Esse tipo de organização dos objetos de banco de dados deixa a finalidade de tabelas, procedures, funções, etc, muito mais intuitiva.

Uma forma bem clássica de organizar objetos é em projetos de BI. É bem comum que arquitetos de BI utilizem schemas dim (Dimensão), fato (Tabelas Fato), stg (Staging) e outros schemas para organizar as camadas e finalidades de cada tabela, onde, por exemplo, a tabela stg.Clientes é a tabela “Clientes” que foi importada na origem e carregada na camada de Staging e a tabela dim.Clientes já é a dimensão de clientes, uma tabela já tratada e preparada para ser utilizada nos projetos de BI.

Controle de acesso e Segurança

Outra finalidade muito importante do uso de schemas é quando falamos em controle de acessos e segurança. Como você deve saber, no SQL Server podemos liberar permissões em tabelas individuais (Ex: GRANT SELECT ON dbo.Tabela TO usuario) e também a nível global no database (Ex: GRANT SELECT TO usuario), onde ele terá acesso em todos os objetos desse banco de dados.

Esse tipo de abordagem acaba dificultando o controle de acessos em ambientes com centenas ou milhares de objetos quando todos estão no mesmo schema, pois ou você tem que analisar cada objeto individualmente ou libera permissão em todos.

Uma forma de melhorar o controle sobre as permissões de objetos é utilizando os schemas, atuando como um nível intermediário entre o acesso individual e o acesso global, onde você pode liberar permissão de SELECT somente no schema cliente, por exemplo. O usuário que recebeu essa permissão poderá consultar todas as tabelas que estão no schema cliente, mas apenas esses objetos (a não ser que ele tenha outras permissões).

Quando você agrupa seus objetos utilizando um schema por departamento, finalidade ou por sistema, por exemplo, o controle acaba ficando muito mais simples do que ter que controlar individualmente cada tabela ou Stored Procedure.

Tabelas “privadas”

Outra finalidade de se utilizar schemas é para a criação de tabelas “privadas”, onde as tabelas são criadas, por padrão, em um schema igual ao nome do usuário ao se utilizar autenticação AD e owner padrão não definido.

Isso faz com que o usuário possa criar tabelas de teste com qualquer nome que ele queira, já que apesar da tabela [dbo].Clientes existir, a tabela [DOMINIO\dirceu.resende].Clientes não. Além disso, fica fácil identificar de quem são determinadas tabelas “temporárias” criadas no database.

Tudo isso sem impactar outros usuários ou a aplicação.

FAQ: Dúvidas básicas sobre Schemas

Onde ficam os schemas do meu banco de dados?

Exibição dos schemas do database de exemplo

Como as tabelas ficam organizadas nos schemas?

Como consulto dados de tabelas com esquemas personalizados?

Como crio um novo schema?

Como altero o schema padrão de um usuário?


Como identificar usuários com schema padrão igual a nome de usuário

No dia a dia do DBA, é comum encontrar ambientes onde o schema padrão de alguns usuários com autenticação Windows não foi definido na criação, sendo atribuindo assim, um novo schema igual ao nome desse usuário. Como resultado, usuários que não tem tanto conhecimento de banco de dados acabam criando vários objetos privados no ambiente (sem saber) e reclamando com o DBA que outros processos e usuários não enxergam as tabelas que eles criam.

Para te ajudar nessa missão, vou disponibilizar um script simples para identificar qual o schema de cada usuário e quem é o owner desses schemas.

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:

E agora, como identificar os usuários com schema padrão diferente do padrão (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:

Como identificar usuários que são donos de algum schema

Outra terefa bem comum, é identificar schemas que são propriedades de determinados usuários. Esse cenário impede a exclusão de usuários, com a mensagem de erro abaixo:

Msg 15138, Level 16, State 1, Line 41
The database principal owns a schema in the database, and cannot be dropped.

Para conseguir excluir esse usuário, você terá que, antes disso, excluir os schemas onde ele é o owner ou migrar a propriedade destes schemas para outro usuário. O script abaixo vai te ajudar a identificar quais são esses schemas:

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:

Como alterar o schema padrão em todos os databases

Para aplicar o script anterior em todos os databases, vamos recorrer à conhecida 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:

Como identificar objetos criados em schemas diferentes do padrão

Outra tarefa comum no dia a dia do DBA é identificar objetos que foram criados em schemas de usuários e movê-los para o schema dbo (ou outro qualquer), quando necessário. Para isso, vou disponibilizar o script abaixo:

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:

E se eu quiser mover os objetos de um schema para outro ?

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:

Stored Procedure para unificar schema padrão e objetos

Caso você esteja buscando uma solução bem prática para redefinir o schema padrão de todos os usuários para o padrão (dbo) e mover todos os objetos privados para o schema dbo também, utilize a Stored Procedure abaixo:

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

Exemplo de uso:

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

Resultado:

Referências
– 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