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

Lesson Learned #55: Azure SQL Managed Instance – Backup failed for Server “servername”. Failed: 1117 -The request could not be performed because of an I/O device error
January 13, 2019

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

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!

News Reporter
News Reporter
Head of Operations (Banking), Director IT Governance, Teamlead Microsoft, Service Delivery Manager. Interested in Office 365, LAMP, IT Security and much more!