SQL Server Enterprise 2016
ArcGIS 10.5.1
We are moving from Oracle to SQL Server and have installed under the 'sde' schema, not dbo. Following our security model, we have a data owner, data editors and data viewers. The Data Owner called 'SDECREATOR' owns data in the geodatabase, and then assigned permissions as needed to editors and viewers.
The data owner is a sql login and database user that has CONNECT, CREATE PROCEDURE, CREATE TABLE, CREATE VIEW. The account owns data, but inside of SSMS, it can't see users to grant to, or see what grants have been given. For example, the viewing account is SDEVIEW. It can issue GRANT SELECT ON MYFC to SDEVIEW, and it works, but looking at properties doesn't show anything.
I believe this to be related to now having the correct permissions. If I had the role db_owner to SDECREATOR, it can see what it needs to. However, since this role an do a lot more than own and manage data, I really don't want to do that.
Is there another way around this?
Sherrie
Hi Sherrie,
You are certainly on the right track with your workflow here. I might recommend a slight change though in the implementation. See more below.
Granting permissions from the database side is a valid workflow for a lot of our users. However, it requires that the user granting those permissions have some pretty high level clearance, usually the SA user or database owner. It also means that those users that are granted permissions have those permissions across the entire database. There are ways around this last part but I won't muddy this up with that.
Contrast that with ArcGIS, we allow you to set permissions at the feature dataset and feature class level. In this case, you could simply make a connection as the SDECREATOR user in the Catalog pane of ArcMap, right click the desired feature dataset or class, and go to Manage > Privileges. As the data owner, you can grant privileges on that data to any user with just the standard data owner privileges on the database side that you mentioned above. Of course, the downside here, is that you would need to do this for all data so it could be a lot of work if there is a lot of data to set permissions to. The Change Privileges geoprocessing tool would help speed this up though, allowing changes to multiple datasets at once.
If using ArcGIS to give permissions to your editors and viewers is not your desired workflow, that's OK. But I would recommend then that you use the SA user or another database administrator user to assign privileges to your users in SSMS. Keeping with the rule of least privileges that you guys are already following, this provides a level of checks and balances rather than giving your SDECREATOR user more privileges than they need.
Some links on this below but let me know if you have any questions for me.
Permissions (Database Engine) - SQL Server | Microsoft Docs
Privileges for geodatabases in SQL Server—Help | ArcGIS Desktop
Grant and revoke dataset privileges—ArcGIS Help | ArcGIS Desktop
Jonathan