topic Re: SQL Server 2008 R2 roles and permissions (delete) in Data Management Questions

SQL Server 2008 R2 roles and permissions (delete)

DanielTruessel — Wed, 13 Feb 2013 13:52:35 GMT

I�??m grateful for any kind of help. 🙂

I�??ll try to explain the problem:

We set up a Windows SQL Server 2008 R2. Furthermore we use Windows authentication and the system tables are stored in the schema of dbo. We have a geodatabase (enabled with the gp tool) called �??test�?�, which is not versioned. We added some logins, created users and corresponding schemas. We also created some roles to grant users specific permissions. So far, everything worked well.

We would now like to have a user called �??gismanger�?�. He is assigned to db_ddladmin, db_securityadmin, db_datareader and db_datawriter roles and moreover he has the database privileges CREATE TABLE, CREATE PROCEDURE and CREATE VIEW (therefore, we created the role db_creator) and EXECUTE.
As we now start ArcCatalog as �??gismanager�?� and we make a connection to the gdb �??test�?�, we can e.g. see all the Feature Classes created by the other users. But we don�??t have the right to delete them (the word delete is grayed out), and that�??s our problem.

How can we provide the user �??gismanger�?� with the permission to delete any table in the gdb? When we add him to the role db_owner, it works, but that�??s not what we want, as he will have too many rights.

Thanks in advance.

Re: SQL Server 2008 R2 roles and permissions (delete)

LeoDonahue — Wed, 13 Feb 2013 17:05:43 GMT

if "gismanager" didn't load the data, doesn't own it, you won't be able to delete it with that account, even if you give it delete permissions.

According to the online help:
http://help.arcgis.com/en/arcgisserver/10.0/help/arcgis_server_dotnet_help/002q/002q0000002z000000.htm

Only the owner of a dataset can drop the dataset or alter its definition; therefore, even if the owner of the dataset grants INSERT, UPDATE, and DELETE privileges on a dataset to another user, that user cannot alter the schema of the dataset.

Re: SQL Server 2008 R2 roles and permissions (delete)

DanielTruessel — Wed, 13 Feb 2013 18:17:47 GMT

Hi,

Thanks a lot for your fast response!

Maybe I'm still a little bit confused about the concept. But isn't there a difference between the "permissions" you grant within the SQL Server Management Studio and the "privileges" you grant on a single dataset within ArcCatalog?

And when I add the "gismanager" to the fixed database role "db_owner", he is able to delete the dataset within ArcCatalog even he is not the owner of the dataset itself. So there should be a permission which enables a user to delete datasets of other users.
(But as you wrote, he cannot change the "privileges", since he is not the owner).

Regards

Re: SQL Server 2008 R2 roles and permissions (delete)

LeoDonahue — Wed, 13 Feb 2013 18:40:08 GMT

There are several posts about this topic in this forum. It comes down to schema.

I usually use ArcCatalog to grant permissions to my datasets, as the user is either reading them or editing them. However, I don't let users own data for this reason. I have a generic account to load all data in our geodatabase, and i don't even give that user edit permissions.

Probably when you put "gismanager" in the db_owner role, it gave that user ability to delete other users schema.

Re: SQL Server 2008 R2 roles and permissions (delete)

DanielTruessel — Fri, 15 Feb 2013 12:49:09 GMT

[�?�] It comes down to schema.

I�??m not an expert and new to this topic. But I�??m aware of the concept of database schemas and the way they exist since SQL Server 2005 �?? that they are not the same as the database user. And that an object in a database belongs to a schema and the schema in turn is owned by a user.

I usually use ArcCatalog to grant permissions to my datasets, as the user is either reading them or editing them. However, I don't let users own data for this reason. I have a generic account to load all data in our geodatabase, and i don't even give that user edit permissions.

I totally agree that this makes sense in most cases and it sounds like a good approach. However, we�??ll probably have the situation that several users will write data in the gdb. In this case we�??d like to have a user with sufficient permissions to �??control�?� the situation (in ArcCatalog) and who is able to delete others data.
Furthermore we�??d like to control the permissions of the users via roles and not by changing privileges on single datasets. This could be an option in some specific cases. I guess that otherwise it can get quickly confusing if you have a lot of data and a lot of different users.

Probably when you put "gismanager" in the db_owner role, it gave that user ability to delete other users schema.

It definitely works. When we add the �??gismanager�?� to the role �??db_owner�?�, he�??s able to delete others data. In other words, he can delete objects owned by others schema.
What we�??d like to know is: which permission gives him the right to do that. And is there another possibility besides adding him to the role �??db_owner�?�.

Re: SQL Server 2008 R2 roles and permissions (delete)

LeoDonahue — Fri, 15 Feb 2013 13:20:56 GMT

If you don't hold me accountable for what happens (because I haven't tried this), you can try granting schema permissions for those other users.

http://msdn.microsoft.com/en-us/library/ms187940.aspx