Security

After configuring the database and any other additional settings used in your ecosystem, you will want to think about security, in terms of who will have access to run sp_execute_external_script.

You can directly create security settings on the external procedure. In this case, you will need to add database permissions to execute external script to the user.

A simple SQL login will look like this:

USE [master]
GO
CREATE LOGIN [RR1] WITH PASSWORD=N'Read!2$17', DEFAULT_DATABASE=[SQLR], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [RR1]
GO
CREATE DATABASE SQLR;
GO
    
USE [SQLR]
GO
CREATE USER [RR1] FOR LOGIN [RR1]
GO
USE [SQLR]
GO
ALTER USER [RR1] WITH DEFAULT_SCHEMA=[dbo]
GO

And now, let's start the external procedure:

    
EXECUTE AS USER = 'RR1';
GO
    
EXEC sp_execute_external_script
      @language = N'R'
      ,@script = N'OutputDataSet<- InputDataSet'
      ,@input_data_1 = N'SELECT 1 AS Numb UNION ALL SELECT 2;'
WITH RESULT SETS
((
    Res INT
))
    
    
REVERT;
GO

And the error message will be, that the user RR1 does not have permissions:

Msg 297, Level 16, State 101, Procedure sp_execute_external_script, Line 1 [Batch Start Line 34]
The user does not have permission to perform this action.

You also have to grant the database a datareader role in order to execute the sp_execute_external_script command:

USE [SQLR]
GO
ALTER ROLE [db_datareader] ADD MEMBER [RR1]
GO

You should also check that executing external scripts is enabled:

GRANT EXECUTE ANY EXTERNAL SCRIPT TO [RR1];
GO  

After setting the database role and granting execute permissions, rerun the sp_execute_external_script procedure and the result of executing the external script should be as follows:

How to manage user authentication (Windows or SQL) and primary security principles; it should be aligned using local DBA, SysAdmin, and architect to help you delegate who will have access to the system.

A rule of thumb is to prepare stored procedures for dealing with different levels of data manipulation and granting access on the level of the stored procedure. Clean the data using this commands:

DROP USER RR1; 
GO 
USE [master]; 
GO 
DROP LOGIN RR1; 
GO 
--DROP TABLE IF EXISTS SQLR; 
GO