Setting Global Permissions for ArcSDE (SQL Server 2008 R2)

Question

Hi there,

I am trying to work out whether there is a way to set Global permissions for viewer and editor roles so that they cannot create new data in the database.

Some context:

I am on v10.1, using an ArcSDE database with SQL Server 2008 R2 back-end.

I have set up 3 roles (Viewer, Editor and Creator) through ArcCatalog, and have also set up some users that I have assigned to these roles (one account that is used to create data, a few editor accounts, and as many viewer accounts as are required).

Having created a Feature Dataset with the 'Creator' account, and set the permissions on the dataset appropriately, I have verified that neither the editor account or the viewer accounts can delete or add data to the feature dataset - All good so far!

However, I discovered that both the viewer and editor accounts ARE able to create their own data in the root of the geodatabase, and then do whatever they want with that data. Not really what I want!!

I have also tried using SQL Server Management Studio to limit permissions on the roles, but to no avail.

Any ideas?

Thanks and kind regards,

Ben

BenStephenson · Answer

Many thanks Asrujit - I'll take a look at that.Ben,

Open SQL Server management Studio--> Connect to the Instance-->Expand Databases-->R-Click on the required database-->Properties-->Permissions-->Select the user you want to revoke the permissions from--> Scroll down in the permissions list below and revoke the permissions under the "GRANT" tab here.

Mainly, the user shouldn't have:
CREATE FUNCTION
CREATE PROCEDURE
CREATE TABLE
CREATE VIEW

Hope this helps!

Regards,

BenStephenson · Answer

Many thanks again Shannon - most helpful.

I'm fairly familiar with using SSMS write SQL queries to get to data, but haven't done any DBA work so the REVOKE/GRANT syntax is what was seeking.

I will review the documentation you provided, too.

Again - thank you.
If you are going to be administrating a geodatabase on SQL Server I strongly suggest becoming comfortable using Management Studio and basic SQL. The tools provided in ArcGIS are useful under very specific circumstances, and only cover a few tasks.

If you go to either the properties of a database (permissions page), or the properties of a user (securables page), you'll be able to see a list of database-level DDL permissions that have been assigned to them, either explicitly, or effectively by way of role membership. Here you can grant, revoke or deny permission.

But, the easier way is to use the query window and execute something like this:

REVOKE CREATE TABLE, CREATE PROCEDURE, CREATE VIEW FROM myuser

For more information on users in geodatabases there are several topics in the Geodata section of the online documentation. I'd start here: 
http://resources.arcgis.com/en/help/main/10.1/index.html#/What_are_user_privileges/002q0000002r000000/
and here:
http://resources.arcgis.com/en/help/main/10.1/index.html#/User_privileges_for_geodatabases_in_SQL_Server/002q0000002s000000/

-Shannon

AsrujitSengupta · Answer

Ben,

Open SQL Server management Studio--> Connect to the Instance-->Expand Databases-->R-Click on the required database-->Properties-->Permissions-->Select the user you want to revoke the permissions from--> Scroll down in the permissions list below and revoke the permissions under the "GRANT" tab here.

Mainly, the user shouldn't have:
CREATE FUNCTION
CREATE PROCEDURE
CREATE TABLE
CREATE VIEW

Hope this helps!

Regards,