topic Re: Same User and Default Schema Name? in Data Management Questions

Same User and Default Schema Name?

TimHayes — Mon, 24 Mar 2014 22:32:54 GMT

I am using ArcGIS 10.2.1, SQL Server 2008 R2, and the dbo schema.

I was reading that ArcGIS SQL Server Enterprise Geodatabase requires the User and Default Schema to be the same name.

Ideally, you shouldn't have the user & default schema w/ the same name because when a user leave the job, the account will be deleted for security reasons, and all of the info for that user will be gone.

Any thoughts on how to deal with what I mention here?

Re: Same User and Default Schema Name?

VinceAngelo — Mon, 24 Mar 2014 23:13:46 GMT

I always use the SDE schema, and use database logins for data owners and
application browser accounts. From there, there are many options for managing
user access.

- V

Re: Same User and Default Schema Name?

Anonymous User — Tue, 25 Mar 2014 00:47:59 GMT

Timothy,

You have a few options to minimize your risk of losing data and/or operating in a less secure environment when users leave your organization.

The most common scenario I come across in enterprise environments is the following hierarchy:

1. Have a secure Geodatabase administrator, the SDE user.
2. Have as few data owner schemas as is required, that are centrally managed and that the DBA or database team is aware of.
3. Create/enable user accounts and grant privileges as needed to select, analyze, print, edit. publish, etc. the data when required.
4. When a user changes roles or leaves the organization, simply alter or revoke privileges as needed, or add/remove their participation in a database role.
5. This model can be further controlled with the use of versioning in a versioned environment.

If your business needs require most users to regularly create and own new tables and feature classes instead of working on a planned geodatabase schema, then I would recommend putting in place a policy in which user accounts are disabled at the database level for a period of time when his or her role changes in order for a data migration to take place to another schema. This policy leads to collaboration with your DBA or IT Management team, and Esri Technical Support is available to assist with any problems that may arise during data migration.

I hope that is helpful. Please let me know if I understood your question properly.

-Matt

Re: Same User and Default Schema Name?

AsrujitSengupta — Tue, 25 Mar 2014 10:38:19 GMT

Tim,

According to me, the most convenient way of dealing with this scenario would be to use a common "database authenticated Login" to load/create new data and then granting sufficient permissions/privileges to individual users to edit the data.

This way, even if an user leaves the job, just deleting their Login would be sufficient and not affect the data in any way directly!