Faaala guys !!
In this post I would like to address a very common subject in DBA's everyday life, which is the default schema of users in the database, which may or may not differ from the standard (dbo), as defined by TI.
What is schema in SQL Server? What is it for?
Click here to view the content of this topic.Schema is basically a collection of objects that aims to group various objects of a department, area or purpose, either for table organization, Stored Procedures, etc., or for access control and security reasons.
Table Organization
One of the purposes of using a schema is to better organize the objects in the database. When all of your objects are in the same schema, there can only be one “Addresses” table, for example. If you try to create a new table with the same name, SQL Server will alert you that a table with that name already exists and interrupt your command.
When we use schemas to organize the objects, we can have a table “Addresses” in the client schema, which are the addresses of customers and we can have the table “Addresses” in the Suppliers schema, which are the addresses of the company's suppliers. This type of organization of database objects makes the purpose of tables, procedures, functions, etc., much more intuitive.
A very classic way of organizing objects is in BI projects. It is quite common for BI architects to use dim (Dimension), fact (Fact Tables), stg (Staging) and other schemas to organize the layers and purposes of each table, where, for example, the stg table. Customers is the table “Customers” that was imported at the source and loaded into the Staging layer and the dim.Clientes table is already the customers dimension, a table already treated and prepared to be used in BI projects.
Access Control and Security
Another very important purpose of using schemas is when we talk about access control and security. As you may know, in SQL Server we can release permissions on individual tables (Ex: GRANT SELECT ON dbo.Table TO user) and also globally on the database (Ex: GRANT SELECT TO user), where it will have access on all objects. of this database.
This kind of approach makes it difficult to control access in environments with hundreds or thousands of objects when they are all in the same schema because either you have to parse each object individually or allow permission on all.
One way to improve control over object permissions is by using schemas, acting as an intermediate level between individual access and global access, where you can release SELECT permission only on the client schema, for example. The user who has been granted this permission can query all tables that are in the client schema, but only those objects (unless they have other permissions).
When you group your objects using a schema by department, purpose, or system, for example, control is much simpler than having to individually control each table or Stored Procedure.
“Private” tables
Another purpose of using schemas is to create “private” tables, where tables are created, by default, in a schema equal to the user's name when using AD authentication and default owner not defined.
This means that the user can create test tables with any name he wants, since despite the [dbo] .Clients table, the [DOMINIO \ dirceu.resende] table. In addition, it is easy to identify whose "temporary" tables are created in the database.
All without impacting other users or the application.
FAQ: Basic Schemas Questions
Where are the schemas in my database?
Displaying sample database schemas
How are tables organized in schemas?
How do I query table data with custom schemas?
How do I create a new schema?
How do I change a user's default schema?
How to identify users with default schema equal to username
In the everyday life of DBA, it is common to find environments where the default schema of some users with Windows authentication was not defined at creation, thus assigning a new schema equal to that user's name. As a result, users who are not as database aware end up creating many private objects in the environment (unknowingly) and complaining to the DBA that other processes and users do not see the tables they create.
To help you with this quest, I will provide a simple script to identify which schema each user owns and who owns these 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 |
Result:
And now, how to identify users with non-standard default schema (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 |
Result:
How to identify users who own some schema
Another very common task is to identify schemas that are properties of certain users. This scenario prevents deletion of users, with the error message below:
Msg 15138, Level 16, State 1, Line 41
The main database owns a schema in the database, and cannot be dropped.
To be able to delete this user, you must first delete the schemas where he is the owner or migrate ownership of these schemas to another user. The script below will help you identify what these schemas are:
|
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 |
Result:
How to change the default schema in all databases
To apply the previous script to all databases, let's use the well-known 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 |
Result:
How to identify objects created in non-standard schemas
Another common everyday DBA task is to identify objects that were created in user schemas and move them to the dbo (or any other) schema as needed. For this, I will make available the script below:
|
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 |
Result:
What if I want to move objects from one schema to another?
|
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 |
Result:
Stored Procedure to Unify Default Schema and Objects
If you are looking for a very practical solution to reset all users default schema to default (dbo) and move all private objects to dbo schema as well, use the Stored Procedure below:
|
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 |
Example of use:
|
1 2 3 4 |
EXEC dbo.stpRedefine_Schema_Padrao @Fl_Altera_Usuarios = 1, -- bit @Fl_Altera_Objetos = 1, -- bit @Fl_Debug = 1 -- bit |
Result:
References
- 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
