topic Re: SQL database OS permissions in Data Management Questions

SQL database OS permissions

MohammedElsayed — Sun, 04 Apr 2021 10:43:39 GMT

We created an SQL database schema for a group of users to edit in, but the problem is when they login using the operating system they cannot see each others edits (ex. user X cannot see the feature classes that user Y created).

The question is: Is there any way to let all the users see each others using the operating system login?

Re: SQL database OS permissions

DavidPike — Sun, 04 Apr 2021 15:45:38 GMT

Are we talking edits of existing features within a feature class or new feature classes created in the schema?

Is any type of versioning employed? If these a newly created feature classes, depending on your permissions design, the feature class would have to be added to a role - with the role being associated with a group of users.

Re: SQL database OS permissions

MohammedElsayed — Sun, 04 Apr 2021 19:58:02 GMT

we are talking about new feature classes created in the schema

no there is no versioning employed

would you clarify the point of adding the FC to a role and what is the best permission design i should use?

Re: SQL database OS permissions

MohammedElsayed — Sun, 04 Apr 2021 19:59:11 GMT

@YasserMahdy1 @YMahdy

Re: SQL database OS permissions

George_Thompson — Mon, 05 Apr 2021 11:29:22 GMT

When they are creating the new feature classes, what is the naming convention being used?

Are the O/S users creating the feature classes or are they already created by a DB user?

Re: SQL database OS permissions

MohammedElsayed — Mon, 05 Apr 2021 12:01:03 GMT

when creating the feature class by default it starts with the database name then the user name then the FC name (DBname\OSuserName\FCname)

the O/S users create the FC

Re: SQL database OS permissions

George_Thompson — Mon, 05 Apr 2021 12:36:56 GMT

Ok, so each of the users will have grant permissions to the other O/S users (can use AD group) in order to see the new feature classes. Just cause you allowed an AD group to create objects (feature classes) in their own schema that they will be able to see the other objects without permission.

I would recommend that you have a data owner (i.e. GIS / DATA) as a DB user, then grant permissions to the AD group for editing.

Re: SQL database OS permissions

MohammedElsayed — Wed, 07 Apr 2021 13:43:56 GMT

thanks for your reply,

would you please explain how to create the group of users and how to add it to database?

the IT team created a group of users by when we tried to add it using CreateDatabaseUser tool it failed

Re: SQL database OS permissions

George_Thompson — Wed, 07 Apr 2021 14:01:55 GMT

I am not sure that you can add a group of AD users to the DB and have them all create tables under a "single" schema owner, unless they have DBO privileges in at the DB level.

This is why I would recommend that you have a DB user own all the object and perform the maintenance on them. Then you only have to give the AD group from IT the edit permissions.

When using O/S authentication it creates complications related to data owners.