I'm working with a Geodatabase in SQL Server Express (ESSRI Workgroup License). Near as I can tell the only security option is Windows Authentication and utilizing the "DBO" deployment of the Geodatabase.
I'm finding that only the database owner or members of the server role 'sysadmin' can create new content, such as a feature class, that will be of the schema "DBO". For other users associated with the geodatabase, to be able to create content their default schema has to be defined as the same name as their user ID and they have to been assigned to the Geodatabase role "ESRIAdmin". That is, for user <DOMAIN>/<USERNAME> their default schema has to be set to <DOMAIN>/<USERNAME>. When that user creates a new feature class the resulting name appears as follows: <DATABASE NAME>."<DOMAIN>/<USERNAME>".<FEATURE CLASS NAME> .
We periodically run an automated process via Windows Task Scheduler that temporarily renames a feature class and then rebuilds it. If the rebuild fails, the feature class name is renamed back to the original name. When the task runs under a user account that is not the database owner and is not a member of the server role 'sysadmin' and the target feature class is schema "DBO" then the rename fails and a new feature class cannot be created. For both actions the error is something to the effect of "the default schema does not match the user schema". However, if the account is added to the server role 'sysadmin' then that account can create/rename the feature class in schema "DBO". The task is also successful if the default schema for the account is set to <DOMAIN>/<USERNAME> .
The goal is to maintain the feature class in the "DBO" schema. We would rather not have task user account be a member of 'sysadmin". And we would rather that all objects in the Geodatabase be of the "DBO" schema. And we would rather not have the account running the automated process be the database owner.
Is it possible to set database privileges for a user account such that a feature class can be created/renamed in the "DBO" schema without having to be a member of the server role 'sysadmin"? I'm guessing it is since 'sysadmin' membership produces the desired result. I just can't work out the combination of required privileges.
Being a member of the database role "ESRIAdmin" is not enough privilege to create new content unless the default schema for the account is set to <DOMAIN>/<USERNAME> or if <DOMAIN>/<USERNAME> is a member of the server role sysadmin.
Using T-SQL I have used run GRANT ALTER, GRANT CREATE TABLE, GRANT ALTER ANY SCHEMA, GRANT CREATE SCHEMA. None of those have solved this.
Any help much appreciated.
