SQL Server 2005 xp_cmdshell

SQL Server 2005 中引入的 xp_cmdshell 选项是服务器配置选项,
使系统管理员能够控制是否可以在系统上执行 xp_cmdshell 扩展存储过程。

1. 如何在sql server 2005 中开启xp_cmdshell

1
2
3
4
5
6
7
8
9
10
11
12
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

2. 如何查询sql server 2005 拓展存储过程xp_cmdshell 状态

1
2
SELECT * FROM sys.configurations where name='xp_cmdshell'
GO

查看value的值, 1 为开启, 0 为关闭

3. 如何查看public角色对哪些拓展存储过程有执行权限

1
2
3
4
5
6
select sysusers.name, sysobjects.name, sysprotects.action from sysobjects, sysusers, sysprotects
where sysobjects.id=sysprotects.id and sysprotects.uid = sysusers.uid and
sysprotects.protecttype=205 and sysprotects.action= 224 and (sysusers.name='public' or
sysusers.name='guest' ) and sysobjects.type='X'

GO

4. 如何去掉public角色对拓展存储过程的执行权限

1
2
deny execute on xp_dirtree to public
GO