SQL Server blocked access to the process "Sys.xp_cmdshell" of the component "xp_cmdshell" because this component was shut down as part of this server's security configuration.

Today in the creation of the database, suddenly found that the xp_cmdshell stored procedures can not be used, a search on the internet, found that most of the security configuration is only closed, and then the following

Code: specific to see the note, it is worth mentioning is = = "Reconfigure with override, the above sentence if not add this sentence, it is only temporarily available, will not affect the original configuration of the system (can be understood as = =" does not add is new and added is Override)

The code is affixed:

--Create a directory (error if the specified path does not exist) exec sp_configure'Show advanced Options',1--Show advanced options reconfigure withOverride--RECONFIGURE exec sp_configure'xp_cmdshell',1--1 on behalf of allow, 0 for block reconfigure withOverrideexec xp_cmdshell'mkdir F:\Work\SQL mkdir E:\SQL'exec sp_configure'xp_cmdshell',0reconfigure withOverrideexec sp_configure'Show advanced Options',0reconfigure withOverride

View Code

SQL also paste it, compare this thing must have a context bar:

--Delete the use master if the database existsifExistsSelect* fromsysdatabaseswhereName=n'Lawyerblog') Begindrop Database Lawyerblogend--Create a directory (error if the specified path does not exist) exec sp_configure'Show advanced Options',1--Show advanced options reconfigure withOverride--RECONFIGURE exec sp_configure'xp_cmdshell',1--1 on behalf of allow, 0 for block reconfigure withOverrideexec xp_cmdshell'mkdir F:\Work\SQL mkdir E:\SQL'exec sp_configure'xp_cmdshell',0reconfigure withOverrideexec sp_configure'Show advanced Options',0reconfigure withOverride--CREATE DATABASE Lawyerblogon primary--database file, PRIMARY filegroup (name='Lawyerblog_data', --Logical name Size=10MB,--Initial Size filegrowth=Ten%,            --file Growth maxsize=1024MB,--Maximum value filename=n'F:\Work\SQL\LawyerBlog_Data.mdf'--storage path (including file suffix), filegroup articledata--article filegroups (tables created into different filegroups can share pressure) (name='lawyerblog_data_article', size=10MB, FileGrowth=Ten%, MaxSize=1024mb, filename=n'E:\SQL\LAWYERBLOG_DATA_ARTICLE.NDF') Log on--Journal (name='Lawyerblog_log1', size=5MB, FileGrowth=5%, filename=n'F:\Work\SQL\LawyerBlog_log1.ldf'), (name='lawyerblog_log2', size=5MB, FileGrowth=5%, filename=n'E:\SQL\LawyerBlog_log2.ldf') Go

View Code

Extended:

If it is normal user to have ALTER settings permission to run sp_configure (the general administrator has this permission)

to add a data file or log file to a database

2. Connect to the database engine.

4. On the Standard menu bar, click New Query.

5. Copy and paste the following example into the Query window, and then click Execute. This instance adds a filegroup consisting of two files to the database. This example creates a filegroup test1fg1 in the AdventureWorks2012 database , and then adds two 5MB files to the filegroup.

   Use mastergoalter DATABASE adventureworks2012add FILEGROUP test1fg1; Goalter DATABASE AdventureWorks2012 ADD FILE (NAME=TEST1DAT3, FILENAME='C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\t1dat3.ndf', SIZE=5MB, MAXSIZE=100MB, FileGrowth=5MB), (NAME=Test1dat4, FILENAME='C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\t1dat4.ndf', SIZE=5MB, MAXSIZE=100MB, FileGrowth=5MB) to FILEGROUP Test1fg1; GO

   View Code

SQL Server blocked access to the process "Sys.xp_cmdshell" of the component "xp_cmdshell" because this component was shut down as part of this server's security configuration.