topic Re: What permissions does a SQL Server database user need to create a geodatabase table from ArcGIS Desktop? in Geodatabase Questions

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

Anonymous User — Wed, 04 Mar 2020 21:03:14 GMT

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.

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

George_Thompson — Thu, 05 Mar 2020 12:58:35 GMT

The user name and schema must match within SQL Sever for ArcGIS clients to create objects. As you mentioned, it is possible to do this if the user is a SYSADMIN fixed server role.

Privileges for geodatabases in SQL Server—Help | Documentation

One option would be to create a separate schema, which is recommended. I always recommend to not store "user" data in the same schema as the geodatabase administrator.

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

Anonymous User — Thu, 05 Mar 2020 13:47:26 GMT

Hi George,

Thanks for your response. I agree that this is what the documentation says, but, as you point out, it's also inaccurate (because the requirement stated by the documentation is not true if the login is assigned to the sysadmin role).

So, what I'm trying to determine is under which conditions it's possible for a user to create data in the dbo schema (really, it could be any schema, but for simplicity I'm using dbo). We agree that it's possible to do if the login is assigned to the sysadmin role, and I know that it's possible to do if the user is assigned to the db_owner role in the database in SSMS (which leads to the question -- why then isn't it possible from an ArcGIS Desktop client?). It may also be the case that it's possible simply by GRANT CONTROL ON SCHEMA :: dbo to the user (though I've not verified this yet).

It seems nonsensical to force each user to store data in their own schema. Different users would presumably be working with the same data in the geodatabase, so why would they be forced to work in different schemas? The shared schema doesn't necessarily need to be dbo, but the requirement that each user work in their own schema seems odd.

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

George_Thompson — Thu, 05 Mar 2020 18:33:00 GMT

Well the SYSADMIN role overrides many of the permissions and "rules" of the server.

If you created a user (with the permissions to created data in ArcGIS) and gave it a matching schema, i.e. DATA/DATA, then you could have the DATA user create the new tables in that schema. I am not sure why it is this way, but can say it has been this way since the beginning of the old "SDE" and SQL Server. When you apply roles, like db_owner it can also cause some other potential unintended consequences with our software.

My recommendation is to have a single schema own the data and then have other users edit that data. If you have multiple people (users) that can "own" the objects I could see that being a mess with multiple "data owners" of an object. I do not see that as an odd requirement for each owner to have a different schema.

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

Anonymous User — Fri, 06 Mar 2020 13:25:11 GMT

Hi George,

Thanks for your response.

Yep, I understand the recommendation. It works this way because that's how it works by default in SQL Server. However, as I indicated, it is possible to configure SQL Server to allow other users to create objects in the DBO schema as well. I'm working on a specific client requirement that two "power users" be able to create tables in the DBO schema. So, what I'm looking for are the specific technical constraints that the ArcGIS Desktop client creates, and, as you say, what are the potential unintended consequences resulting from those.

Thanks,

Jon.

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

George_Thompson — Mon, 09 Mar 2020 12:22:31 GMT

To answer your question: I am not sure if it is possible to have other "non-DBO" users create objects in the DBO schema.

Does your client have a requirement that all the logins be OS authentication (i.e. AD)?

Have you also reviewed this doc that talks about the differences between DBO vs. SDE schema?

A comparison of geodatabase owners in SQL Server—Manage geodatabases in SQL Server | Documentation

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

Anonymous User — Mon, 09 Mar 2020 15:17:33 GMT

Hi George,

OK, thanks for this reference. As it turns out, I should be able to do what I want, according to this doc:

As an aside, these are database logins, not AD logins.

So, I'm creating the database user and giving it membership in the db_owner role:

and explicitly assigning the default schema of DBO to the user:

Which is all I need to be able to create a table in SSMS as testuser in DBO. However, ArcMap complains when I try to create a table as testuser that the user and schema names don't match. Whether ArcMap is adding this additional constraint, or this is an incorrect error message, who knows?

Now, the document that you referenced also says this:

which seems to contradict what it says above (that it's either SYADMIN or database owner) rather than both.

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

George_Thompson — Mon, 09 Mar 2020 16:04:02 GMT

At this point, I would reach out to technical support and work with a geodatabase analyst and reference this thread.