sp_execute_external_script and Permissions

This post will (hopefully) be short and sweet. It came about as I was testing out “stuff” for the Install R Packages in SQL Server ML Services series of posts and I could not get it to work as I had expected.

Background

Usually, when I work with SQL Server Machine Learning Services, I execute code in the context of admin (yeah I know, do not do that :)). In the Install R Packages in SQL Server ML Services series I used non-admin accounts, and all of a sudden nothing worked.

I tried to research (read Google) the issue, but I could not find a definitive answer, just tidbits here and there. So when I finally realised what the issues were, I decided to write a blog post about it.

Housekeeping

As in quite a few of my other blog posts, here follows some code to set things up if you want to follow along.

Code

This is the code to “set the scene”:

IF NOT EXISTS(SELECT 1 FROM sys.server_principals 
              WHERE name = 'user1')
BEGIN
  CREATE LOGIN user1
  WITH PASSWORD = 'password1234$';
END

DROP DATABASE IF EXISTS PermissionDB;
GO

CREATE DATABASE PermissionDB;
GO

USE PermissionDB;
GO

CREATE USER user1
FROM LOGIN user1;
GO

Code Snippet 1: Create Login, Database and User

In Code Snippet 1 we create a login, a database, and then we create a user for the login in the database.

NOTE: Below you see in quite a few places the abbreviation SPEES. That is short for sp_excute_external_script.

Permissions

What we see in Code Snippet 1 is that we have not assigned user1 to any particular roles on either the server or the database, so user1 has whatever default permissions he gets during creation. Let us look in SSMS UI and see what server level roles user1 belongs to:

Figure 1: Roles User1

In Figure 1 we see how user1 belongs to the server role public, and that is the only role he belongs to. So what if we have code like so:

--uncomment the following and execute
--to execute as user1
--EXECUTE AS USER = 'user1';
--GO

EXECUTE sp_execute_external_script
             @language = N'R'
           , @script = N'
               d<-42
               OutputDataSet <- as.data.frame(d)'

--to switch back from user1 uncomment and execute
--the following
--REVERT

Code Snippet 2: Simple Test Code

The code in Code Snippet 2 allows you to switch between admin/sa and user1 without having to log in as user1.

If someone with sufficient permissions ran the code in Code Snippet 2, the result looks like so:

Figure 2: sa Executes SPEES

As we see in Figure 2 we get back The Answer to the Ultimate Question of Life, the Universe and Everything., but if user1 runs the same code, the result is:

Figure 3: SPEES Execution Error

Oh, dear me, user1 receives a permission denied exception! Well, from what we know about SQL Server and permissions it probably was not that unexpected. No problem, we know about SQL Server permissions, so we realise we probably have to GRANT EXECUTE permissions on SPEES to user1 (or public):

GRANT EXECUTE ON sp_execute_external_script to user1;

Code Snippet 3: Grant Execute Permission

Being in the database where user1 exists and executing the code in Code Snippet 3 as admin/sa - what could possibly go wrong:

Figure 4: Grant Permission Error

Oops, something did go wrong, as it turns out that if you try to grant permissions on extended stored procedures, which SPEES is, you need to do it from the master database. Cool, let us switch to master and do it there. Well, if you try to do that - then you get another error: the user does not exist in master, sigh!

At this stage you have a couple of options:

• Add the login for the user to the sysadmin role, or the user to the db_owner role in the actual database. No do not do that, I am only kidding! Do.Not.Do.That!
• Create the user in master and grant the permission. That would work.
• Grant the permission to public.

Both options above (I do not count sysadmin, db_owner) have drawbacks:

• Create the user in master: you now have a user in master, and the question is what “shenanigans” the user can do.
• Grant permission to public: anyone can potentially execute SPEES, not ideal.

For reasons that become clear later I go with granting permission to public:

USE master
GRANT EXECUTE ON sp_execute_external_script to public;

Code Snippet 4: Granting Permission to Public

After admin/sa runs the code in Code Snippet 4, user1 can now execute the code in Code Snippet 2 and we should see The Answer …:

Figure 5: SPEES Execution Error

Eish, what goes on here? We did grant the permission in Code Snippet 4, so what now? Hmm, if we compare the errors, we see that they are slightly different. The error before granting the permission is something like: “The EXECUTE permission was denied …”, whereas the error after granting the permission is like: “The user does not have permission …”. It seems that the code in Code Snippet 4 did something, but we still miss a piece (or multiple) of the puzzle, and it is permissions related. What permission(s) is the question?

So I decided to try a “brute force attack”; find all built in permissions in SQL Server, browse through them and see if I see something promising. For this, I used a SQL Server function: sys.fn_builtin_permissions, which - when executed - returns a description of the built-in permissions hierarchy of the server:

SELECT * FROM fn_builtin_permissions('database')

Code Snippet 5: Retrieve All Functions

The parameter (database) in Code Snippet 5 indicates what permissions I want back. In this case, I want all permissions on a database level. When I ran the code in Code Snippet 5 the function call returned 78 rows, and towards the end of the result I saw something promising:

Figure 6: SPEES Execution Error

The highligthed part in Figure 6 looks very interesting. I wonder what happens if I do something like this as admin/sa in the database the user is in:

GRANT EXECUTE ANY EXTERNAL SCRIPT TO user1

Code Snippet 5: Grant External Script

The code in Code Snippet 5 ran without any issues, and user1 can then try following code:

SELECT SUSER_NAME()
EXECUTE sp_execute_external_script
             @language = N'R'
           , @script = N'
               d<-42
               OutputDataSet <- as.data.frame(d)'

Code Snippet 6: Another Try by user1

The SELECT SUSER_NAME() in Code Snippet 6 is there to verify that it is the correct user executing. The result when user1 executes looks like so:

Figure 6: Successful Execution

So that was the missing link: GRANT EXECUTE ANY EXTERNAL SCRIPT TO ..., and to tell the truth; afterwards, I have seen a few posts on the net mentioning EXECUTE ANY EXTERNAL SCRIPT.

One more thing: above I mentioned that I favour granting the EXECUTE on SPEES to public instead of adding the user to master. The reason for this is what we just have seen: yes you do a “blanket” GRANT by granting public, but a user still needs to be granted EXECUTE ANY EXTERNAL SCRIPT before he can “go wild”. That gives admins/dba’s some control over who can execute SPEES.

Summary

To allow a non-admin database user to execute sp_execute_external_script you need to:

• Grant public execute permissions on sp_execute_external_script, and you do it in master. Obviously, you only need to do it once.
• Grant EXECUTE ANY EXTERNAL SCRIPT to the user in the different databases he needs to execute SPEES in.

That is it!

One final thing: if you want to read more about sp_execute_external_script, SQL Server R Services series has some posts.

~ Finally

If you have comments, questions etc., please comment on this post or ping me.

Blog Feed: