Is it possible to create different schema in same enterprise geodatabase (sql server)?

1528
7
Jump to solution
09-25-2017 12:28 AM
Highlighted
Occasional Contributor

Dear everyone, i have created my enterprise geodatabase under dbo schema in sql server. I want to store different projects data under different schema. If I now create 10 schema for 10 projects (for example, schema name : projectA , and related data will be stored under this schema) , is it possible? I am not finding a way to store featureclass and tables under different schema while I created my geodatabase under dbo or sde.

Thanks

Regards

Muqit

Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
MVP Regular Contributor

Yes, that is possible. Just connect as the concerned user and create\load data. (for example, schema name : projectA , and related data should be stored under this schema.....so connect as projectA in ArcCatalog and create\load data).

Make sure that the logins(data owners) do NOT have 'sysadmin' server role granted on them. If the logins have sysadmin, then the data will all show up under DBO and owned by DBO schema.

Privileges for geodatabases in SQL Server

View solution in original post

7 Replies
Highlighted
MVP Regular Contributor

Yes, that is possible. Just connect as the concerned user and create\load data. (for example, schema name : projectA , and related data should be stored under this schema.....so connect as projectA in ArcCatalog and create\load data).

Make sure that the logins(data owners) do NOT have 'sysadmin' server role granted on them. If the logins have sysadmin, then the data will all show up under DBO and owned by DBO schema.

Privileges for geodatabases in SQL Server

View solution in original post

Highlighted
Occasional Contributor

Thanks Mr. Sengupta, I have another open discussions regarding data management in enterprise geodatabase, but no one is replying me. Can you please tell me how I can shifted my attribute tables inside enterprise gdb in sql server with all primary and foreign keys? I have tranferred all my tables but facing some trouble regarding some ID fields are converting as OBJECTID, Date fields are missing (because gdb has date type - datetime2) and all the primary key and foreignkeys have been lost.

Reply
0 Kudos
Highlighted
MVP Regular Contributor

Muqit If this question has been answered, please close the thread by marking the valid response as 'Mark Correct'.

I‌ will check the other thread you mention and reply back if I have any suggestions.

Reply
0 Kudos
Highlighted
Occasional Contributor

facing some trouble again, my username: zoa_implamint and schema: implamint, when I login through this user by arccatalog and want to create a table or any object is giving me error as below:#

Should the login name and schema name be same everytime?

Reply
0 Kudos
Highlighted
MVP Regular Contributor

Yes, the login and schema should match. So use 'zoa_implamint' as Schema and try.

Add logins and users to SQL Server—Help | ArcGIS Desktop 

In Microsoft SQL Server, database administrators add logins to the SQL Server instance, and these logins are mapped to users in individual databases on the SQL Server instance. Database users who will create tables and feature classes must have privileges necessary to create these objects in the database, and they must have a schema in which they can create them. When using ArcGIS, that schema must have the same name as the database user.

Highlighted
Occasional Contributor

I want to have many user unde one schema, is it possible? I know same user name as schema works, but this is not the solution, if we want to have many user under one schema then what to do?

Reply
0 Kudos
Highlighted
MVP Regular Contributor
  • Multiple Windows Login using same schema -- Not possible.
  • Multiple Database Authenticated Login using same schema -- Not possible

If you want many users to use the same schema, then use 1 common Database Authenticated Login to connect and create\load data. 

Example: 5 users use a login named GISOwner, which has schema 'GISOwner', and use that to connect and create\load data. This way whatever data these 5 users create\load, everything will be under 'GISOwner'.