Insufficient permissions on Feature Service (Execute permissions)

[vijaybadugu]

I have recently created a Service with Feature capability enabled and  same is used in survey123 for add new feature, 

Whenever I submit the survey, I usually see the below error message in arcgis server logs. I could go in to SQL Server and provide some manual permissions on SQL Server for published user.  Is there a way to set automatically this permissions  whenever a service is automatically published without opening a SQL Server ?

Error: Insufficient permissions [42000:[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The EXECUTE permission was denied on the object 'i<id>_get_ids', database '<database>', schema 'dbo'.] [<featureClass>].

[MarceloMarques · Accepted solution]

You can create a new role in the SQL Server database and then grant the privileges to the role, any user that the role is granted will get the privileges, if you create a new featureclass, table or if you perform any geodatabase operation that creates new tables in the database then you will need to refresh the role with new privileges.

Privileges for geodatabases in SQL Server—ArcGIS Pro | Documentation

Grant and revoke dataset privileges in databases and enterprise geodatabases—ArcGIS Pro | Documentat...

If you read my Database Guide Book for SQL Server and if you download my Database Template Script for SQL Server, then you will learn how to create the roles and grant the permissions directly in SQL Server.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

Note: use the Production Mapping Guide Books. Those are generic and apply to any industry.

[vijaybadugu]

I resolved the issue by adding user to the executor role 

[MarceloMarques]

If you add the user to the executor role then the user will be able to execute any store procedure in the database, you can restrict this to enhance security and minimize the risk of SQL injection threats, you can grant execute only on the store procedures of the geodatabase data owner users instead.

In this example we are granting permission through the schema, this means is not necessary to refresh the roles with new permissions if a new table gets created.

USE pm
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 pm
GO
EXEC sp_addrole 'pm_viewer', 'pm'
GO
GRANT SELECT ON SCHEMA::[pm] TO [pm_viewer]
GO

Grant role to the users.

USE pm
GO
/*-------------------------------------------------------------------------
-- Create pmeditor
-------------------------------------------------------------------------*/
CREATE LOGIN [pmeditor] WITH PASSWORD = 'pmeditor', 
DEFAULT_DATABASE=[pm],DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC sp_adduser 'pmeditor'
GO
EXEC sp_change_users_login 'update_one','pmeditor','pmeditor'
GO
----------------------------------------------------------------------------
--Containment - if using containment create users this way
CREATE USER [pmeditor] WITH PASSWORD=N'pmeditor'
GO
CREATE SCHEMA [pmeditor] AUTHORIZATION [pmeditor]
GO
ALTER USER [pmeditor] WITH DEFAULT_SCHEMA=[pmeditor]
GO
----------------------------------------------------------------------------
EXEC sp_addrolemember N'pm_editor', N'pmeditor'
GO
---------------------------------------------------------
/*-------------------------------------------------------------------------
-- Create pmviewer
-------------------------------------------------------------------------*/
CREATE LOGIN [pmviewer] WITH PASSWORD = 'pmviewer', 
DEFAULT_DATABASE=[pm],DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC sp_adduser 'pmviewer'
GO
EXEC sp_change_users_login 'update_one','pmviewer','pmviewer'
GO
----------------------------------------------------------------------------
--Containment - if using containment create users this way
CREATE USER [pmviewer] WITH PASSWORD=N'pmviewer'
GO
CREATE SCHEMA [pmviewer] AUTHORIZATION [pmviewer]
GO
ALTER USER [pmviewer] WITH DEFAULT_SCHEMA=[pmviewer]
GO
----------------------------------------------------------------------------
EXEC sp_addrolemember N'pm_viewer', N'pmviewer'
GO
---------------------------------------------------------

[MarceloMarques]

If you do not want to grant permissions via the schema to have more control then use this script to refresh the roles permissions.

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

[MarceloMarques]

You can also use the ArcGIS Pro Privileges Tool to refresh the roles permissions.

Grant and revoke dataset privileges in databases and enterprise geodatabases—ArcGIS Pro | Documentat...