AnsweredAssumed Answered

What permissions does a SQL Server database user need to create a geodatabase table from ArcGIS Desktop?

Question asked by jon.bailey on Mar 4, 2020
Latest reply on Mar 9, 2020 by George_Thompson-esristaff

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:

  1. Set its default schema in the geodatabase that I created to dbo.
  2. 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.

Outcomes