Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 536 other subscribers

Blog Views

1.645.428 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 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') November 5th, 2020
  • 25 January 2019
  • 0
  • Database DMVs and Catalog Views Maintenance SQL Server

SQL Server - How to identify how to change the default schema for database users

Views: 2.386
Reading Time: 8 minutes

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.
Before proceeding with the main theme of this article, I will make a brief explanation of what the schema object is in SQL Server and some ways to benefit from using this type of object.

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.

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

Result:

And now, how to identify users with non-standard default schema (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

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:

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

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:

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

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:

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

Result:

What if I want to move objects from one schema to another?

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

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:

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

Example of use:

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

Result:

Taking up 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

tags: schemasqlsql server

You may also like ...

  • FREE Database Check-Up + Security Analysis: Do You Need It?

  • SQL Server - How to Integrate Database with Ryver and Send Messages Using CLR (C #)

  • Webcast - 300 Celebration posts + SQL Server 2019 What's New (04 / 10 / 2018 - 21h)

  • Next SQL Server 2014 - How to Estimate Progress and How Much Time Is Left to Create an Index
  • Previous [FREE and ONLINE Event] - Power BI Web Scraping - Capturing information from MVPs Brazil - 24/01/2019 at 20:30

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.