Lesson Learned #54: The specified schema name name@domain.com either does not exist or you do not have permission to use it

Published 03-13-2019 07:35 PM 3,490 Views
First published on MSDN on Jan 13, 2019
Hello Team,

Some days ago, I worked in a very interesting service request.

Our customer created a Azure Active Directory group adding two members and they added this group as db_owner role of the database.

The first member of this group is able to create tables without problems  using CREATE TABLE TableExample (id int) but the second or others ones are not able and they are facing the following error: Msg 2760, Level 16, State 1, Line 1 [The specified schema name name@domain.com either does not exist or you do not have permission to use it.]

It seems that starting the second member of the group, the default schema that SQL Engine is using is the user account not dbo.

In order to mitigate the issue, we have two solutions:

  • Specify the schema name in the name of the table , like this one: CREATE TABLE dbo.TableExample (id int) every member of the group are able to without problem.

  • Specify the schema default name at the moment of the creation of the group using the default_schema option.


Enjoy!
1 Comment
Occasional Visitor

Hello @Jose_Manuel_Jurado , having encountered the issue you described, I am wondering: how can I inspect the default_schema of an existing Active Directory group?

I work with a couple of Azure SQL databases tied to different directories. In one of these databases, when I 'select SCHEMA_NAME()' I see 'dbo' while in the other the same query returns 'null'. However, when I look at the default_schema_name column of the relevant Active Directory groups in sys.database_principals, it is 'null' in *both* cases. This has me rather confused!

%3CLINGO-SUB%20id%3D%22lingo-sub-369152%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2354%3A%20The%20specified%20schema%20name%20name%40domain.com%20either%20does%20not%20exist%20or%20you%20do%20not%20have%20permission%20to%20use%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369152%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Jan%2013%2C%202019%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20Hello%20Team%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Some%20days%20ago%2C%20I%20worked%20in%20a%20very%20interesting%26nbsp%3Bservice%20request.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Our%20customer%20created%20a%20Azure%20Active%20Directory%26nbsp%3Bgroup%20adding%20two%20members%20and%20they%20added%20this%20group%20as%20db_owner%20role%20of%20the%20database.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20The%20first%20member%20of%20this%20group%20is%20able%20to%20create%20tables%20without%20problems%20%26nbsp%3Busing%20%3CSTRONG%3E%20CREATE%20TABLE%20TableExample%20(id%20int)%20%3C%2FSTRONG%3E%20but%20the%20second%20or%20others%20ones%20are%20not%20able%20and%20they%20are%20facing%20the%20following%20error%3A%20%3CSTRONG%3E%20Msg%202760%2C%20Level%2016%2C%20State%201%2C%20Line%201%20%5BThe%20specified%20schema%20name%20name%40domain.com%20either%20does%20not%20exist%20or%20you%20do%20not%20have%20permission%20to%20use%20it.%5D%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20It%20seems%20that%20starting%20the%20second%20member%20of%20the%20group%2C%20the%20default%20schema%20that%20SQL%20Engine%20is%20using%20is%20the%20user%20account%20not%20dbo.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20In%20order%20to%20mitigate%20the%20issue%2C%20we%20have%20two%20solutions%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3ESpecify%20the%20schema%20name%20in%20the%20name%20of%20the%20table%20%3C%2FSTRONG%3E%20%2C%20like%20this%20one%3A%20%3CSTRONG%3E%20CREATE%20TABLE%20dbo.TableExample%20(id%20int)%20%3C%2FSTRONG%3E%20every%20member%20of%20the%20group%20are%20able%20to%20without%20problem.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3ESpecify%20the%20schema%20default%20name%20%3C%2FSTRONG%3E%20at%20the%20moment%20of%20the%20creation%20of%20the%20group%20using%20the%20default_schema%20option.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20Enjoy!%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-369152%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jan%2013%2C%202019%20Hello%20Team%2CSome%20days%20ago%2C%20I%20worked%20in%20a%20very%20interesting%26nbsp%3Bservice%20request.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-369152%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E2760%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Active%20Directory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ethe%20specified%20schema%20name%20namedomain%20com%20either%20does%20not%20exist%20or%20you%20do%20not%20have%20permission%20to%20use%20it%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2308486%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%2354%3A%20The%20specified%20schema%20name%20name%40domain.com%20either%20does%20not%20exist%20or%20you%20do%20no%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2308486%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F247500%22%20target%3D%22_blank%22%3E%40Jose_Manuel_Jurado%3C%2FA%3E%26nbsp%3B%2C%20having%20encountered%20the%20issue%20you%20described%2C%20I%20am%20wondering%3A%20how%20can%20I%20inspect%20the%20default_schema%20of%20an%20existing%20Active%20Directory%20group%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20work%20with%20a%20couple%20of%20Azure%20SQL%20databases%20tied%20to%20different%20directories.%20In%20one%20of%20these%20databases%2C%20when%20I%20'select%20SCHEMA_NAME()'%20I%20see%20'dbo'%20while%20in%20the%20other%20the%20same%20query%20returns%20'null'.%20However%2C%20when%20I%20look%20at%20the%20default_schema_name%20column%20of%20the%20relevant%20Active%20Directory%20groups%20in%20sys.database_principals%2C%20it%20is%20'null'%20in%20*both*%20cases.%20This%20has%20me%20rather%20confused!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Mar 13 2019 07:35 PM
Updated by: