Setting Global Permissions for ArcSDE (SQL Server 2008 R2)

[BenStephenson]

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

[ShannonShields]

Ben,

did you use the Create Database User GP tool to create the User accounts? If so, those users will automatically be granted the ability to create tables, procedures & views in the geodatabase. http://resources.arcgis.com/en/help/main/10.1/index.html#//00170000017r000000

You can revoke those permissions using Management Studio for those users who you don't want creating their own tables.

-Shannon

[BenStephenson]

Shannon - hi.

Many thanks for your reply. I did indeed use that tool to create the user accounts.

I guess I'm not familiar enough with Management Studio to work out how to revoke those permissions (I tried setting their database role membership only to db_datareader but that didn't appear to do anything).

Do you have any guidance on how to revoke the permissions correctly in SSMS?

Thanks again - really appreciate it!

Kind regards,

Ben

[ShannonShields]

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/002q0000002r00000...
and here:
http://resources.arcgis.com/en/help/main/10.1/index.html#/User_privileges_for_geodatabases_in_SQL_Se...

-Shannon

[AsrujitSengupta]

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]

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/002q0000002r00000...
and here:
http://resources.arcgis.com/en/help/main/10.1/index.html#/User_privileges_for_geodatabases_in_SQL_Se...

-Shannon

[BenStephenson]

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,