SDE in SQL Server and data owner permissions

[SherrieKubis]

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 

[JonathanFarmer_oldaccount]

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

[SherrieKubis]

Hi Jonathon, thank you for the response.  We have been doing SDE (I still cal it by this name, sorry), for a very long time, but always in Oracle. Now we are migrating to SQL Server.  Our SDE Administrators are considered trusted users, they own their data and are very knowledgeable.  In Oracle they don't have DBA or SYSDBA, but can do their work without issue.  They do a lot from ArcGIS, but they also do a lot in TOAD, as they write ETL scripts that join spatial and tabular data.  When moving them to MS SQL, they need the same access, which means a 'sa' or 'dbo' user.  Having a DBA or another account perform the grants it's feasible.  I've been looking for grants that would allow them to see users and what permissions have been granted to them, and allow them to travel around investigating schema data with SELECT access.  They have a lot to do with this migration, over what they normally do, and I want to make it as smooth for them as possible without giving away higher privileges.  That means allowing them to see users, privileges assigned to users, and schema data for investigative purposes.

Any other insights would be appreciated ... but I fear the worst in that they are going to need db_owner.

Sherrie

[JonathanFarmer_oldaccount]

Hi Sherrie,

I'll look into this some more and see want options I can find. It all depends on how Microsoft has structured things so I'll need to do some research. I haven't dealt much with granting privileges on the database side with users other than a member of sysadmin or db_owner.

Jonathan

[SherrieKubis]

Thanks Jonathan.  I'm researching as well, will update if I find anything. 

Sherrie

[GangWang]

Jonathan and Sherrie, can you please share what privileges from database side are requries for a data viewer? I have tried either of the followings but still couldn't see the feature classes created by Test_Creator in TestSchema. dbo is the owner of TestSchema and Test_Creator/Test_Viewer defaults to TestSchema. The weird thing is that if i log in SSMS with Test_Viewer i can see the tables created by Test_Creator. By they way, we are using sde 10.5.1 and SQL server 2014.

GRANT SELECT ON SCHEMA::TestSchema TO Test_Viewer

or

ALTER ROLE db_datareader ADD MEMBER Test_Viewer

[JonathanFarmer_oldaccount]

The SELECT privilege is all a data viewer needs in SQL Server: http://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-sql-server/privileges-sqlserver.htm#E...

If you want to test if this is related to granting permissions on the schema, try granting permissions just to the underlying table and see if they can view it then. I typically don't alter the default roles in SQL Server, it's probably better to setup your own custom role if you want to go that route.

Jonathan 

[GangWang]

Jonathan, thanks for your quick response. I have tried what you suggested "GRANT SELECT ON TestSchema.Test TO Test_Viewer" from database side and still couldn't see the feature class from ArcCatalog. I have also tried ArcCatalog -> SDE connection with Test_Creator -> Feature class -> Manage -> Privileges -> grant select and no luck either. But I can see the table when I log in SSMS with Test_Viewer. Any ideas?

[JonathanFarmer_oldaccount]

Hmm... that's odd.

I've seen behavior before where a user had to grant editing privileges to a feature class for anyone to be able to see it. I'll need to go back through our cases and see if I can find that and how we fixed it.

To see if this is that issue, try 2 things:

1. Grant SELECT, INSERT, UPDATE, DELETE and EXECUTE to this user and see what happens
2. Also try giving the DB_OWNER database role and see what happens

But you shouldn't need either of these just to view a feature class. This is just a test to see what we get (if you can view anything at all with this user after giving it full privileges).

[MichaelVolz]

Jonathan:

Was the necessity to grant editing privileges to a feature class for anyone to see it considered a BUG as this seems like a serious data security risk?