Skip to content

Dirceu Resende

DBA SQL Server e Analista de BI (PowerBI, SSAS, SSIS, SSRS)

  • Consultoria
    • Consultoria de BI
    • Consultoria de Power BI
    • Consultoria SQL Server
  • Arquivo
  • Séries
    • Certificação
    • Segurança e Auditoria
    • Performance Tuning
    • O que mudou no T-SQL ?
    • Proteção de Dados
  • Vagas de Emprego
  • Eventos Data Platform
  • Sobre
  • Contato

Other Languages

Assinar blog por e-mail

Digite seu endereço de e-mail para assinar este blog e receber notificações de novas publicações por e-mail.

Junte-se a 536 outros assinantes

Visualizações do Blog

1.645.428 views

Categorias

  • Apache / .htaccess (9)
  • Banco de Dados (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Auditoria (15)
      • Azure (2)
      • CLR (53)
      • Desenvolvimento de Query (83)
      • DMVs e Views de Catálogo (31)
      • Erros (22)
      • Ferramentas (12)
      • Formatação e Validação de Dados (23)
      • Funcionalidades pouco conhecidas (19)
      • Hacks (17)
      • Integrações (30)
      • Manipulação de Arquivos (13)
      • Manutenção (80)
      • Monitoramento (35)
      • O que não fazer (7)
      • OLE Automation (19)
      • Performance Tuning (22)
      • Python (1)
      • Segurança (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Carreira e Cursos (13)
  • Carreira, Cursos e Certificações (28)
  • Celulares (1)
  • Eventos e Palestras (63)
  • Programação (57)
    • C# (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • Javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Sem categoria (10)
  • SEO (4)
  • Virtualização (5)

Microsoft MVP Data Platform

Minhas Certificações

Treinamentos

Arquivo de Posts

Posts recentes

  • Descontos da “Black Friday” nos Treinamentos de SQL Server (Comprem meu curso kkkkk) 27 de novembro de 2020
  • SQL Server – As “novas” funções GREATEST e LEAST 27 de novembro de 2020
  • SQL Server – Como saber a data do último login de um usuário 9 de novembro de 2020
  • Azure na Prática Gratuito #07 – Administrando Banco de Dados no Azure 5 de novembro de 2020
  • Analysis Services – An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file ‘tmcachemanager.cpp’, function ‘TMCacheManager::CreateEmptyCollectionsForAllParents’) 5 de novembro de 2020
  • 25 de janeiro de 2019
  • 0
  • Banco de Dados DMVs e Views de Catálogo Manutenção SQL Server

SQL Server – Como identificar a alterar o schema padrão dos usuários do banco de dados

Visualizações: 2.386
Tempo de Leitura: 8 minutos

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.

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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):

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 ?

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
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:

Transact-SQL
1
2
3
4
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

Tags: schemasqlsql server

You may also like...

  • Check-up GRATUITO do seu banco de dados + análise de segurança: Será que você precisa ?

  • SQL Server – Como fazer uma integração do banco de dados com o Ryver e enviar mensagens utilizando CLR (C#)

  • Webcast – Comemoração 300 posts + Novidades do SQL Server 2019 (04/10/2018 – 21h)

  • Next SQL Server 2014 – Como estimar o andamento e quanto tempo falta para a criação de um índice
  • Previous [Evento GRATUITO e ONLINE] – Power BI Web Scraping – Capturando informações dos MVPs Brasil – 24/01/2019 às 20:30

Deixe uma resposta Cancelar resposta

Dirceu Resende © 2020. All Rights Reserved.