Grant the Execute permission on stored procedures associated with the data to be edited - SQL geodatabase

[AndreaB_]

Hi all,

I have seen this statement referenced several times in the esri documentation: "Grant the Execute permission on stored procedures associated with the data to be edited". Here's one example: https://support.esri.com/en-us/knowledge-base/the-create-feature-task-could-not-be-completed-insuffi... 

here's another one: Data Editor minimum privileges: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/privileges-sqlserv... 

here's info I found about Granting the permisson: https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/grant-permissions-on-a-... 

My question is: What are the stored procedures associated with the data to be edited? If I look in SSMS, I see tons of sde owned procedures. 

and more. But which ones do I need to grant permissions on?

Thank you!

[MarceloMarques · Accepted solution]

@AndreaB_ 

The best practice is to not load the data with the "SDE" user because the "SDE" user is the ArcSDE Repository Owner, and the "SDE" user shall be used only for Geodatabase Administrative tasks.

With this covered, let's assumed you followed the database best practices from my database guide books for Production Mapping and followed all the best practices in my database template scripts as well, and you have created the data owner user, then loaded the data using the data owner user and then created the database roles to grant permissions of the data owner user tables to the editor and viewer users.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices (esri.com)

• Guide Books ArcGIS Pro
• Database Template Scripts
• Database Connections Best Practices

Then, there are 3 ways you can refresh the permissions for the roles that were granted to the editor and viewer users.

Option A: Use the ArcGIS Pro Privileges Tool

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/overview/grant-dataset-privileges.ht...

Privileges Tool will automatically grant the execute privilege for the store procedures of the data owner user featureclasses and tables.

Option B: Grant Privileges to roles via the SQL Server database user Schema

--pm - data owner user
--pm_editor - role for editor users
--pm_viewer - role for viewer users
--pmeditor - editor user
--pmviewer - viewer user

USE pmdb
GO
GRANT DELETE ON SCHEMA::[pm] TO [pm_editor]
GRANT EXECUTE ON SCHEMA::[pm] TO [pm_editor]
GRANT INSERT ON SCHEMA::[pm] TO [pm_editor]
GRANT SELECT ON SCHEMA::[pm] TO [pm_editor]
GRANT UPDATE ON SCHEMA::[pm] TO [pm_editor]
GO
GRANT SELECT ON SCHEMA::[pm] TO [pm_viewer]
GO

USE pmdb
GO
EXEC sp_adduser 'pmeditor'
GO
EXEC sp_change_users_login 'update_one','pmeditor','pmeditor'
GO
EXEC sp_addrolemember N'pm_editor', N'pmeditor'
GO
EXEC sp_adduser 'pmviewer'
GO
EXEC sp_change_users_login 'update_one','pmviewer','pmviewer'
GO
EXEC sp_addrolemember N'pm_viewer', N'pmviewer'
GO

The privileges are automatically refreshed, this eliminates the need to have to refresh the privileges when new featureclasses or tables are created, or when a geodatabase operation is performed that creates new tables in the database, such as with "register as traditional versioning" and "enable geodatabase archiving". Note, some GP Tools might not like when privileges are granted via the schema like in the example above, this is well known limitation for some of the raster tools for example, there is an enhancement for this, but the workaround is to grant "insert, update, delete, execute" privileges direct to the role instead of using the "schema" like in the example above.

Option C: T-SQL Script to refresh the permissions of the database roles

USE pm
GO
DECLARE @OWNER varchar(10)
SET @OWNER = 'pm'
DECLARE Tables_Cursor CURSOR
READ_ONLY
FOR SELECT a.name as table_name, a.xtype as type 
    FROM dbo.sysobjects a, dbo.sysusers b
    WHERE a.uid = b.uid and a.xtype in ('U','P') and b.name = @OWNER ORDER BY a.name
DECLARE @name varchar(100), @type varchar(1)
OPEN Tables_Cursor
FETCH NEXT FROM Tables_Cursor INTO @name, @type
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        --PRINT @owner + '.' + @name + ' ' + @type
        -- GRANT PERMISSIONS TO TABLE       
        IF @type = 'U'
        BEGIN    
            EXECUTE ('GRANT SELECT, INSERT, UPDATE, DELETE ON ' + @OWNER + '.' + @name + ' TO pm_editor')
            EXECUTE ('GRANT SELECT ON ' + @OWNER + '.' + @name + ' TO pm_viewer')			
        END
        ELSE
           --GRANT PERMISSION TO STORE PROCEDURE
           IF @type = 'P'
           BEGIN    
               EXECUTE ('GRANT EXEC ON ' + @OWNER + '.' + @name + ' TO pm_editor') 
           END
    END
    FETCH NEXT FROM Tables_Cursor INTO @name, @type
END
CLOSE Tables_Cursor
DEALLOCATE Tables_Cursor
GO

Now, which are the store procedures that you need to grant execute permission. Those are not the ArcSDE Repository Store Procedures.
Those are the data owner user featureclasses and tables Store Procedures, see screenshot below.

I hope this clarifies your question.

[MarceloMarques · Accepted solution]

@AndreaB_  the database template scripts that you can download go beyond the database guide books and below is an example of the database script that you will find to create the SQL Server Geodatabase following the best practices to setup the "sde" user, the data owner user, the editor and viewer users.

• Database Template Scripts 
  
  

  SQL Server 
   SQL Server 2019 / 2017 / 2016 / 2014 (Windows/Linux)
       ArcGIS Pro 3.x & ArcMap 10.8.x  June 21, 2022

  

USE pm
GO
CREATE USER [sde] FOR LOGIN [sde_pm]
GO
CREATE USER [pm] FOR LOGIN [pm]
GO
----------------------------------------------------------------------------
--Containment - if using containment create users this way
CREATE USER [sde] WITH PASSWORD=N'sdeadmin'
GO
CREATE USER [pm] WITH PASSWORD=N'pmadmin'
GO
----------------------------------------------------------------------------
CREATE SCHEMA [sde] AUTHORIZATION [sde]
GO
CREATE SCHEMA [pm] AUTHORIZATION [pm]
GO
----------------------------------------------------------------------------
ALTER USER [sde] WITH DEFAULT_SCHEMA=[sde]
GO
ALTER USER [pm] WITH DEFAULT_SCHEMA=[pm]
GO

----------------------------------------------------------------------------
--ArcSDE administrator
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--DO NOT NEED TO GRANT DB_ONWER TO CREATE SDE REPOSITORY
--EXEC sp_addrolemember N'db_owner', N'sde'
--GO
--EXEC sp_addrolemember N'db_owner', N'pm'
--GO
----------------------------------------------------------------------------

EXEC sp_droprolemember 'gis_sde_owner', 'sde'
GO
DROP ROLE gis_sde_owner
GO
CREATE ROLE gis_sde_owner AUTHORIZATION dbo
GO
GRANT CREATE TABLE TO gis_sde_owner;
GRANT CREATE PROCEDURE TO gis_sde_owner;
GRANT CREATE FUNCTION TO gis_sde_owner;
GRANT CREATE VIEW TO gis_sde_owner;
GO
EXEC sp_addrolemember 'gis_sde_owner', 'sde'
GO
--Verify role
EXEC sp_helprolemember 'gis_sde_owner'
GO
--Verify role permissions
select dp.NAME AS principal_name,
           dp.type_desc AS principal_type_desc,
           o.NAME AS object_name,
           p.permission_name,
           p.state_desc AS permission_state_desc 
   from    sys.database_permissions p
   left    OUTER JOIN sys.all_objects o
   on     p.major_id = o.OBJECT_ID
   inner   JOIN sys.database_principals dp
   on     p.grantee_principal_id = dp.principal_id
   where dp.NAME = 'gis_sde_owner'
GO
--User permissions
select USER_NAME(p.grantee_principal_id) AS principal_name,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc 
from    sys.database_permissions p
inner   JOIN sys.database_principals dp
on     p.grantee_principal_id = dp.principal_id
where USER_NAME(p.grantee_principal_id) = 'sde'
GO

--Allows the sde user to kill connections to the geodatabase
EXEC master..sp_addsrvrolemember @loginame = N'sde_pm', @rolename = N'processadmin'
GO

----------------------------------------------------------------------------
--Data Creator
----------------------------------------------------------------------------
USE pm
GO
EXEC sp_droprolemember 'gis_data_creator', 'pm'
GO
DROP ROLE gis_data_creator
GO
--EXEC sp_droprole 'gis_data_creator'
--GO
CREATE ROLE gis_data_creator AUTHORIZATION dbo
GO
GRANT CREATE TABLE TO gis_data_creator
GO
GRANT CREATE PROCEDURE TO gis_data_creator
GO
GRANT CREATE VIEW TO gis_data_creator
GO
EXEC sp_addrolemember 'gis_data_creator', 'pm'
GO
--Verify role
EXEC sp_helprolemember 'gis_data_creator'
GO
--Verify role permissions
select dp.NAME AS principal_name,
           dp.type_desc AS principal_type_desc,
           o.NAME AS object_name,
           p.permission_name,
           p.state_desc AS permission_state_desc 
   from    sys.database_permissions p
   left    OUTER JOIN sys.all_objects o
   on     p.major_id = o.OBJECT_ID
   inner   JOIN sys.database_principals dp
   on     p.grantee_principal_id = dp.principal_id
   where dp.NAME = 'gis_data_creator'
GO
--User permissions
select USER_NAME(p.grantee_principal_id) AS principal_name,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc 
from    sys.database_permissions p
inner   JOIN sys.database_principals dp
on     p.grantee_principal_id = dp.principal_id
where USER_NAME(p.grantee_principal_id) = 'pm'
GO

----------------------------------------------------------------------------
--Change users login
----------------------------------------------------------------------------
USE pm
GO
ALTER USER sde with LOGIN=sde_pm
go
ALTER USER pm with LOGIN=pm
go
--sp_change_users_login 'update_one','sde','sde_pm'
--sp_change_users_login 'update_one','pm','pm'

----------------------------------------------------------------------------
​--pm - data owner user
--pm_editor - role for editor users
--pm_viewer - role for viewer users
--pmeditor - editor user
--pmviewer - viewer user

USE pmdb
GO
EXEC sp_addrole 'pm_editor', 'pm'
GO
GRANT DELETE ON SCHEMA::[pm] TO [pm_editor]
GRANT EXECUTE ON SCHEMA::[pm] TO [pm_editor]
GRANT INSERT ON SCHEMA::[pm] TO [pm_editor]
GRANT SELECT ON SCHEMA::[pm] TO [pm_editor]
GRANT UPDATE ON SCHEMA::[pm] TO [pm_editor]
GO

USE pmdb
GO
EXEC sp_addrole 'pm_viewer', 'pm'
GO
GRANT SELECT ON SCHEMA::[pm] TO [pm_viewer]
GO

USE pmdb
GO
EXEC sp_adduser 'pmeditor'
GO
EXEC sp_change_users_login 'update_one','pmeditor','pmeditor'
GO
EXEC sp_addrolemember N'pm_editor', N'pmeditor'
GO
EXEC sp_adduser 'pmviewer'
GO
EXEC sp_change_users_login 'update_one','pmviewer','pmviewer'
GO
EXEC sp_addrolemember N'pm_viewer', N'pmviewer'
GO

I hope this helps.

[AndreaB_]

Amazing help, thank you @MarceloMarques ! I'm working my way through setting up a new Geodatabase so I am using your best practices and learning a lot as I go. Thanks! (the screenshot I used is from a copy of our existing geodatabase - not best practices 😊 )

One question: does the gisviewer need execute permission, or can I leave that with the role db_datareader?

[MarceloMarques · Accepted solution]

@AndreaB_ 

One question: does the gisviewer need execute permission, or can I leave that with the role db_datareader?

[Marcelo]: no, the gisviewer user does not need execute permission on the store procedures, and do not grant the db_datareader, grant only "select" on the data owner user tables to the role and grant the role to the viewer user.

Note: do not use the roles db_datareader nor the role db_datawriter because you are granting access to every table in the database to the user, avoid doing this for a better security posture.

[AndreaB_]

Ah, got it. I changed that now - I created a gis_viewer role and granted the role to the gisviewer user following your script. Thanks again!