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]

Michael,

It certainly would be if we could reproduce it here. I'm not aware of any defects out there now for this issue though (don't see any in our system).

[GangWang]

Tried the followings and still couldn't see the tables

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
3. Grant Test_Viewer the schema owner of the Test_Schema

Is there anything needed to done at SDE level?

[JonathanFarmer_oldaccount]

Well, I'd say we've got an issue here and it isn't a permissions one. We've just tried giving almost all the permissions possible in SQL Server (db_owner) and it still didn't work.

So then I'd start asking questions like how was this viewer user created? Are there other viewer users in this database that exhibit the same behavior? Are there other ArcGIS clients that you can test connecting with to see if you see the feature class? Things like that.

If you're able, it may not be a bad idea for Tech Support to take a look as well. They can get into the details a lot easier than we can here.

Jonathan

[GangWang]

Here is the SQL used to create the date viewer in SSMS

CREATE LOGIN [Test_Viewer] WITH PASSWORD='********', DEFAULT_DATABASE=[TempInstance1], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

CREATE USER [Test_Viewer] FOR LOGIN [Test_Viewer] with DEFAULT_SCHEMA=[TestSchema]

[GangWang]

Sorry for the confusion. The issue was with that i have checked 'List only objects owned by the connecting user with enterprise databases' in ArcCatalog to avoid seeing relationship classes. The default is off. After I unchecked, I can see feature classes. Thank you Jonathan for your help!

[PrzemyslawDusza]

Sherrie Kubis‌ if you are looking for great tools to manage and work with SQL Server according to GIS and DB best practices take a look a those gratis tools https://github.com/geocom-gis/GeocomDatabaseManagementTools