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 = n'pwd. 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 n' Cmd _ Login -- specifies the context when the stored procedure is executed
AS
EXEC master. sys. xp_mongoshell 'dir c :\'
GO
-- 3. Call the Common Logon of the stored procedure
USE master;
GO
-- 3.a Logon
Create login test
With password = n'abc. 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 = n' test ';
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 logs _login;