My goal is to configure a geodatabase user who is not the dbo user to be able to create tables in the dbo schema.
In ArcMap, I created an enterprise geodatabase on a local SQL Server using the Create Enterprise Geodatabase tool. I then created a geodatabase user, testuser, using the Create Database User tool.
In SQL Server Management Studio, I made a couple of edits to the testuser login:
- Set its default schema in the geodatabase that I created to dbo.
- Assigned the user to the db_owner role in the geodatabase.
Once I did that, then I was able to use the testuser user to create a table in the database in the dbo schema.
However, if I try to create a table in the geodatabase in ArcMap, I get an error that indicates that ArcMap is trying to create the table in the testuser schema, not in the dbo schema.
If I go back into SSMS and assign the testuser login to the sysadmin role, then I can create a table in the dbo schema from ArcMap.
Obviously, I don't want to grant a user sysadmin privileges just so that they can create tables in the dbo schema. So, what specific permissions (apparently over and above db_owner) does a user require so that the user can create tables in the dbo schema from within ArcMap?
In SQL Server Management Studio, if I create a database user and assign it to the db_owner role, it can create objects in schema that is does not own, including dbo. If I set the default schema for the user to dbo, then the user will create items in the dbo schema by default.