Having Trouble Getting Data Creators Group to Function Properly

[AndrewPagano]

Note: This is my first post here - quite excited about it 🙂  So here it is.

Background info - ArcGIS SDE v10, using Active Directory groups and windows authentication for access, SQL 2008 R2 Backend

Relevant Doc - http://help.arcgis.com/en/arcgisserver/10.0/help/arcgis_server_dotnet_help/index.html#/User_permissi...

Question:

I am bringing up a new SDE server and having been fleshing out the access rights according to the document linked above. Thus far I have the data viewers, editors, and admins groups created and functioning properly; however, I am running into issues with the data creators group. I have only one schema (DBO) and wish to keep it that way. Is this possible to have a Creators group that can utilize/work within this one schema? The first table on the above linked document makes it sound like this is not possible, but I do not see the logic in creating a "Creators" group that would not be able to create within a single schema instance.

Pertinent information within the SQL Mgmt Studio:
- Within the Security dropdown of the server instance -> Logins dropdown: I have the "Data Creators" group. The default DB is set to the one schema, the server role is set up with public and dbcreator, and the user mapping has the one schema selected with the
   role membership covering public, db_datareader, and db_datawriter.
- Within the Databases dropdown of the server instance -> Database instance -> Security -> Users: Verified that db_datareader and db_datawriter are checked.
- Within the Databases dropdown of the server instance -> Database instance (Right click -> Permissions): Verified that connect, Create Procedure, and Create Table were checked.

If you have further questions, ask.

Thanks in advance!

~Pagano

[JonDeRose]

Hi Andrew -

From your description of your database config it sounds as though you are using a DBO schema geodatabase and have assigned users to a AD group that has db_datareader/writer assigned along with db_owner.

While this will enable your users to have data ownership permissions within the SQL Server geodatabase, they would not be able to perform the role of data owners from within ArcGIS.

The information outlined in the link: "User permissions for geodatabases in SQL Server" is correct in that data creators only require 'CREATE TABLE' and 'CREATE PROCEDURE' it also references that "User names for any user accounts that own data must have the same name as their default schema."  If using a DBO schema geodatabase, only users, or groups which assign user's sysadmin rights within the SQL Server instance will be able to create data.   While the db_owner role does enable elevated permissions within a database it is not synonymous with being a sysadmin (dbo).

The following link provide some additional information on the dbo ownership of geodatabase ownership:

A comparison of geodatabase owners in SQL Server

Adding Windows-authenticated logins or groups to a SQL Server database

- Jon

[JonathanBailey]

Hi Jon,

Sorry to necro this thread, but I have a question that's closely related:

I have a database in SQL Server using the dbo schema. I have a number of users whom I want to allow to edit existing datasets, but not to create new datasets. Let's call these users "editors".

When I assign the editor users to the db_datawriter role, they can still create datasets in their own schema. This isn't what I want (in fact, I'd rather these users not have their own schema at all, because all of these schema are displayed in another software tool that we're using, which confuses the user).

To remedy this, I changed the default schema for each editor user dbo. This is fine, because it cuts down on the individual user schema in the database. However, when an editor tries to create a new table in ArcCatalog, they get the error message about the user name not matching the schema name. Again, this will confuse users, and what I'd rather have is an error telling the user that they don't have permissions to create tables. 

Is there a recommended way to set this up (i.e., everything in the dbo schema, editors not allowed to create tables)?

Thanks,

Jon.