Allows common users to execute the xp_mongoshell Stored Procedure

Environment requirements:
SQL Server 2005 and later versions

Background

Xp_mongoshell is a very dangerous stored procedure. It can access the resources of the operating system, but sometimes we also need to use it for some special processing.

From the security perspective, disabling xp_eclipsehll is the most secure. Even if you want to use xp_eclipsehll for special purposes, it is best to write user stored procedures that implement this special purpose, only xp_mongoshell is used in this user stored procedure, while common users can only use these user stored procedures.

Correct Solution

The following example shows how to enable a common user to call a stored procedure that contains xp_mongoshell code without the permission to execute the Stored Procedure xp_mongoshell.

-- 1. logon with xp_cmdshell permission
USE master;
GO
-- 1. a. Create a logon
Create login Cmd_Login
With password = NPwd.123,
CHECK_POLICY = OFF;
GO
-- 1. B. This logon is built-in and cannot be logged on. This can reduce security hiding.
DENY CONNECT SQL
TO Cmd_Login;
GO
　　
-- 1. c. Because you want to call xp_mongoshell, you must have a user and permissions in the master.
Create user login _login
For login pai_login
WITH DEFAULT_SCHEMA = dbo;
　　
Grant execute on sys. xp_cmdshell
TO Cmd_Login;
GO
　　
-- 2. User Database
USE tempdb;
GO
　　
-- 2.a creates a user for the logon who executes the xp_cmdshell permission
Create user login _login
For login pai_login
WITH DEFAULT_SCHEMA = dbo;
GO
　　
-- 2. B test the Stored Procedure
Create proc dbo. p
With execute as n1__login -- specifies the context when the stored procedure is executed
AS
EXEC master. sys. xp_cmdshell dir c:
GO
　　
-- 3. Call the Common Logon of the stored procedure
USE master;
GO
-- 3.a Logon
Create login test
With password = Nabc.123,
CHECK_POLICY = OFF;
GO
　　
-- 3. B Database User
USE tempdb;
GO
Create user test
For login test;
GO
　　
-- 3.c permission for executing stored procedures
Grant execute on dbo. p
TO test;
GO
　　
-- 3.d run the test
Execute as login = Ntest;
GO
EXEC dbo. p;
GO
REVERT;
GO
　　
-- 4. delete test
Drop proc dbo. p;
Drop user test;
Drop user Login _login;
　　
USE master;
Drop login test;
Drop user Login _login;
Drop login into _login; additional instructions

In most cases, the database owner is a member of the sysadmin fixed server role of sa. In this case, you can also directly specify the database owner as the security context of the stored procedure execution.