Casos do Dia a Dia – Default Schema e Default Database

E aí pessoas,

No post de hoje, vou mostrar duas situações que já aconteceram no meu dia a dia envolvendo o Default Schema e o Default Database. #gogogo

Default Schema:

Msg 208, Level 16, State 1, Line 3

Invalid object name ‘NomeObjeto’.

Default Database:

Cannot open user default database. Login Failed.

Default Schema – Introdução:

Em primeiro lugar, precisamos entender alguns conceitos sobre o SCHEMA no SQL Server. O link abaixo contém algumas informações muito interessantes:

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server

“User-Schema Separation:

User-schema separation allows for more flexibility in managing database object permissions. A schema is a named container for database objects, which allows you to group objects into separate namespaces. For example, the AdventureWorks sample database contains schemas for Production, Sales, and HumanResources.

The four-part naming syntax for referring to objects specifies the schema name.

Server.Database.DatabaseSchema.DatabaseObject“

Resumindo:

O SCHEMA pode ser bem útil para separar o gerenciamento de objetos de uma database. Por exemplo, você pode liberar o acesso para um usuário somente nas tabelas que fazem parte de um schema específico e deixar as outras tabelas bloqueadas.

Outra informação importante é que a nomenclatura completa de um objeto tem quatro partes. No nosso caso, vamos nos limitar ao “DatabaseSchema.DatabaseObject“.

Por padrão, todo usuário criado utiliza o “dbo” como DEFAULT SCHEMA, contudo, nós podemos definir algum outro se quisermos.

O DEFAULT SCHEMA é a nível de database, ou seja, um usuário pode ter um “schema1” em uma database e o “schema2” em outra.

Default Schema – Testes:

Visto isso, nos testes abaixo vou utilizar dois usuários que possuem o DEFAULT SCHEMA diferentes na database “Traces”.

Usuário: HPSPECTRE\Luiz Vitor

Default Schema: dbo

Utilizando esse usuário, vou criar uma tabela chamada “testeSchemaDBO” sem especificar o SCHEMA na criação.

USE Traces

CREATE TABLE testeSchemaDBO (
	Id INT IDENTITY(1,1),
	Nome VARCHAR(100)
)

INSERT INTO testeSchemaDBO
VALUES ('Vasco da Gama')

USE Traces

CREATE TABLE testeSchemaDBO (

Id INT IDENTITY(1,1),

Nome VARCHAR(100)

)

INSERT INTO testeSchemaDBO

VALUES ('Vasco da Gama')

Usuário: teste

Default Schema: tst

Com esse usuário, vou criar uma tabela chamada “testeSchemaTST” sem especificar o SCHEMA na criação. Depois crio uma outra tabela com o mesmo nome, mas dessa vez utilizando o schema “dbo”. E por fim crio a tabela “testeSchemaTST2” sem especificar o schema.

USE Traces 

CREATE TABLE testeSchemaTST (
	Id INT IDENTITY(1,1),
	Nome VARCHAR(100)
)

INSERT INTO testeSchemaTST
VALUES ('Luiz Vitor')

CREATE TABLE dbo.testeSchemaTST (
	Id INT IDENTITY(1,1),
	Nome VARCHAR(100)
)

INSERT INTO dbo.testeSchemaTST
VALUES ('França Lima')

CREATE TABLE testeSchemaTST2 (
	Id INT IDENTITY(1,1),
	Nome VARCHAR(100)
)

INSERT INTO testeSchemaTST2
VALUES ('Power Tuning')

USE Traces

CREATE TABLE testeSchemaTST (

Id INT IDENTITY(1,1),

Nome VARCHAR(100)

)

INSERT INTO testeSchemaTST

VALUES ('Luiz Vitor')

CREATE TABLE dbo.testeSchemaTST (

Id INT IDENTITY(1,1),

Nome VARCHAR(100)

)

INSERT INTO dbo.testeSchemaTST

VALUES ('França Lima')

CREATE TABLE testeSchemaTST2 (

Id INT IDENTITY(1,1),

Nome VARCHAR(100)

)

INSERT INTO testeSchemaTST2

VALUES ('Power Tuning')

Agora vamos verificar na database as tabelas que foram criadas:

“Eita!!! Mas que bruxaria é essa Luiz?!?! O SQL Server colocou um schema diferente na criação de algumas tabelas!!!”

Pois é, segue algumas observações importantes:

Quando você NÃO ESPECIFICA o SCHEMA na criação dos objetos, eles serão criados utilizando o seu DEFAULT SCHEMA. Por esse motivo, as tabelas “testeSchemaTST” e “testeSchemaTST2” utilizaram o default schema “tst” do usuário “teste”;
Além disso, podemos ter o nome da mesma tabela com schemas diferentes. Temos uma tabela “testeSchemaTST” com o schema “tst” e outra com “dbo” e elas são tabelas DISTINTAS;
Já a outra tabela “testeSchemaDBO” utilizou o default schema “dbo” do outro usuário.

“Que loucura Luiz! E isso pode impactar alguma coisa?”

Infelizmente sim. Agora vamos fazer alguns testes para acessar as tabelas com cada um dos usuários.

OBS: ANTES de executar os SELECTs, tente imaginar qual vai ser o resultado e se algum deles irá falhar!

Usuário: teste

Default Schema: tst

Vamos executar os SELECTs abaixo:

USE Traces 

SELECT * FROM testeSchemaTST

SELECT * FROM dbo.testeSchemaTST

SELECT * FROM testeSchemaDBO

SELECT * FROM testeSchemaTST2

USE Traces

SELECT * FROM testeSchemaTST

SELECT * FROM dbo.testeSchemaTST

SELECT * FROM testeSchemaDBO

SELECT * FROM testeSchemaTST2

Aqui temos alguns comportamentos interessantes:

No primeiro SELECT não informamos o schema, com isso o SQL Server verifica primeiro se a tabela “testeSchemaTST” existe no default schema do usuário que é o “tst”. Ele encontra e retorna o resultado.
No segundo SELECT nós especificamos o schema, com isso o SQL Server vai ir direto na tabela e retorna o resultado. Repare que aqui ele utiliza a tabela do outro schema, ou seja, é uma tabela diferente do resultado anterior.
No terceiro SELECT não informamos o schema, com isso o SQL Server verifica primeiro se a tabela “testeSchemaDBO” existe no default schema do usuário que é o “tst”. Ele não encontra. Com isso, agora ele verifica se a tabela existe no schema “dbo” e retorna o resultado. Aconteceu exatamente o que está no link da documentação que citei no início do post. Aqui alguém pode ter imaginado que aconteceria um erro, mas é uma pegadinha do malandro iéié!!! kkkkkk

No quarto SELECT não informamos o schema, com isso o SQL Server verifica primeiro se a tabela “testeSchemaTST2” existe no default schema do usuário que é o “tst”. Ele encontra e retorna o resultado.

Usuário: HPSPECTRE\Luiz Vitor

Default Schema: dbo

Vamos executar novamente os mesmos SELECTs:

USE Traces 

SELECT * FROM testeSchemaTST

SELECT * FROM dbo.testeSchemaTST

SELECT * FROM testeSchemaDBO

SELECT * FROM testeSchemaTST2

USE Traces

SELECT * FROM testeSchemaTST

SELECT * FROM dbo.testeSchemaTST

SELECT * FROM testeSchemaDBO

SELECT * FROM testeSchemaTST2

Agora vamos analisar esse novo resultado:

No primeiro SELECT não informamos o schema, com isso o SQL Server verifica se a tabela “testeSchemaTST” existe no default schema do usuário que é o “dbo”. Ele encontra e retorna o resultado.
No segundo SELECT nós especificamos o schema, com isso o SQL Server vai ir direto na tabela e retorna o resultado. Observe que nesse caso, a tabela utilizada foi a mesma do primeiro SELECT.
No terceiro SELECT não informamos o schema, com isso o SQL Server verifica se a tabela “testeSchemaDBO” existe no default schema do usuário que é o “dbo”. Ele encontra e retorna o resultado.
No quarto SELECT não informamos o schema, com isso o SQL Server verifica se a tabela “testeSchemaTST2” existe no default schema do usuário que é o “dbo”. Ele não encontra e retorna um erro informando que o objeto não existe. Repare que nesse caso a tabela existe no outro schema “tst”, mas o SQL Server não reconhece automaticamente, pois o default schema do usuário é diferente.

Msg 208, Level 16, State 1, Line 19
Invalid object name 'testeSchemaTST2'.

1 2	Msg 208, Level 16, State 1, Line 19 Invalid object name 'testeSchemaTST2'.

Conclusão – Default Schema:

Como podemos observar, essa questão do DEFAULT SCHEMA pode trazer alguns resultados diferentes dependendo da forma que as queries são executadas e o usuário utilizado. Eu particularmente não gosto de definir isso para algum usuário, justamente para evitar esse tipo de confusão que eu demonstrei com os exemplos.

Tive esse problema em um cliente que usava o DEFAULT SCHEMA para o usuário da aplicação. Todas as queries do sistema não especificavam o schema das tabelas. Como eu estava utilizando um usuário diferente, toda query que eu executava retornava erro e era necessário incluir o schema manualmente para funcionar. Se existisse alguma tabela com o mesmo nome no meu default schema, o resultado também poderia ser diferente.

Por fim, recomendo que sempre especifique o schema nas queries ao invés de utilizar o DEFAULT SCHEMA.

“DatabaseSchema.DatabaseObject“

Default Database:

Por último, vou mostrar um erro relacionado ao DEFAULT DATABASE.

Aqui vamos utilizar o usuário “teste” e definir o default database com a base “Treinamento_TSQL”.

Com isso, quando o usuário “teste” logar no SQL Server e abrir uma query, ela vai utilizar automaticamente a database default. Depois você pode alterar normalmente na query para utilizar outra database, desde que o usuário possua os devidos acessos.

Isso pode ser útil quando você quer liberar o acesso para uma database apenas para um determinado usuário. Por exemplo, uma database de homologação ou teste.

Agora, vou remover o acesso dele na database “Treinamento_TSQL”. Com isso, quando ele tentar logar novamente irá receber o erro abaixo:

Cannot open user default database. Login Failed.
Login failed for user 'teste'.

1 2	Cannot open user default database. Login Failed. Login failed for user 'teste'.

Esse mesmo erro também pode acontecer nas situações abaixo:

Caso a database esteja indisponível (por exemplo, status “Restoring” ou “Offline”);
Caso a database não exista mais. Alguém pode dropar a database e esquecer de alterar o default database de algum usuário;
Caso a database seja restaurada e o usuário fique sem acesso. Isso pode acontecer quando o usuário não existe ou não tem acesso no banco de origem onde foi feito o backup que está sendo restaurado.

Por fim, segue abaixo mais dois links que podem te ajudar a resolver esse tipo de problema:

https://www.mssqltips.com/sqlservertip/2131/sql-server-login-issue-with-default-database/

https://stackoverflow.com/questions/362434/how-can-i-change-my-default-database-in-sql-server-without-using-ms-sql-server-m

Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

Default Schema – Introdução:

Default Schema – Testes:

Conclusão – Default Schema:

Default Database:

Compartilhe isso:

Deixe uma respostaCancelar resposta