First published on MSDN on Jan 13, 2019
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 firstname.lastname@example.org 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.