SQL SERVER-开启xp_cmdshell

在sql server中执行cmd命令，报以下异常。

EXEC master.sys.xp_cmdshell 'dir c:/'

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 44]
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

 

 服务器开启

--Server config

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1;
GO
RECONFIGURE;
GO

 

如果是sysadmin账号则可以执行了，若不是则要做以下配置。

 

添加代理账号，注意一定用windows账号登陆SSMS，在执行

EXEC sp_xp_cmdshell_proxy_account 'adc\ccc','xx'

 否则会报以下异常，其余异常如权限不够，则用管理员启动SSMS或则账号密码错误，还有一定要加域名如果本地则EXEC sp_xp_cmdshell_proxy_account 'adc\xxxi','mima'，一定写成本地主机名才行

An error occurred during the execution of sp_xp_cmdshell_proxy_account.
Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created.
Error code: 0(null), Error Status: 0.

 

在master下建立相应用户并赋予执行xp-cmdshell权限

GRANT EXECUTE ON sys.xp_cmdshell

    TO Cmd_Login;

 

否则报异常

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.