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ópicoComo 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.
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 |
E agora, como identificar os usuários com schema padrão diferente do padrão (dbo):
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 |
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:
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 |
Como alterar o schema padrão em todos os databases
Para aplicar o script anterior em todos os databases, vamos recorrer à conhecida sp_MSforeachdb:
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 |
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:
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 |
E se eu quiser mover os objetos de um schema para outro ?
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 |
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:
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:
1 2 3 4 |
EXEC dbo.stpRedefine_Schema_Padrao @Fl_Altera_Usuarios = 1, -- bit @Fl_Altera_Objetos = 1, -- bit @Fl_Debug = 1 -- bit |
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
Obrigado Dirceu por explicar detalhes de schemas no SQLServer! Show!