Grant and revoke privileges with another user

Question

Hi all,

I would like to manage privileges for my feature datasets inside my enterprise geodatabase (10.2-SQL Server) with another user (Not DBO user nor SDE user).

I'm using windows authentification and assigning db_owner permissions to a specific user in a database level using SQL Management Studio, but it doesn't seems to work.

Is there a way to achieve this?

Thanks for your help,

MB

Marco_AlejandroBonilla · Answer

I've decided to manage all the privileges with only one account as recommended, but I think this topic could be useful for enterprise gdb management, hope in the future we could use something like that.

I'll mark all your comments as helpful, thank you very much for your answers and interest

MB

RussellBrennan · Answer

The WITH GRANT option is only available from the database using database level tools. I would not recommend using this privilege to manage privileges for data in an enterprise geodatabase, especially when data is contained in a feature dataset. There are many cases (too many to list) where an ArcGIS dataset is not just one table but many tables in the underlying DBMS. If you use database tools to manage privileges on these datasets and happen to miss one of the underlying tables you will run into issues reading and writing this data. This can lead to data corruption and a lot of sadness. Please stick to managing privileges for datasets with Esri tools.

The recommended approach to this is to load data using a 'headless' user. This user could be a database user or an OS authenticated user that isn't associated to someone specific in your organization. Loading data as this user will allow you to manage privileges using this user.

Hope this helps,

Russell

AsrujitSengupta · Answer

There is an option of "With Grant" which may help in this scenario.

SQL SERVER – Difference Between GRANT and WITH GRANT | Journey to SQL Authority with Pinal Dave

Welcome to SQLServer MVP Blog: Grant vs With Grant option in SQL Server

This is available in the database properties--> Permissions