SQL Server 2008 R2 roles and permissions (delete)

Question

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.

LeoDonahue · Answer

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

DanielTruessel · Answer

[�?�] 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�?�.

LeoDonahue · Answer

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.