Instead of using xp_cmdshell to execute queries against SQL Server, change your code to use stored procedures, user-defined functions, or even dynamic T-SQL called using sp_executesql. If you need to execute code against a different server, use a linked server.
Finally, consider rewriting your code not to use a cursor if possible. Set-based operations in ...
This command: ECHO 1>C:\Pharmsuite\Vercheck.txt
Does not do what you think it should do because 1> has a special meaning.
This problem can be fixed by adding a space before >:
ECHO 1 >C:\Pharmsuite\Vercheck.txt
Or in your case:
SET @CMD = 'ECHO '+@Ver+' >'+@Vercheck
It also works with this command (making sure there is a space before ECHO [...
Well, you could not grant execute on it explicitly to end users (deny it even), and only enable it in stored procedures that use EXECUTE AS with some login that does have execute permissions. Then grant execute on only that stored procedure to the user that needs to run the command.
First, make sure xp_cmdshell is enabled for the instance:
I just got your code to work on my end.
Assign Full Control NTFS permissions to 'C:\BCPFiles\bcpFile.dat to
the user you will use in SQL Server Management Studio to run the
Re-run your query in SQL Server Management Studio
Or, just to get past the following error try:
Error = [Microsoft][ODBC Driver 11 for SQL Server]...
From xp_cmdshell (Transact-SQL) in the product documentation:
The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential ...
You can also setup a proxy account using sp_xp_cmdshell_proxy_account to allow non-administrators to use xp_cmdshell. This will allow you to setup a less privileged Windows account rather than xp_cmdshell always using the SQL Server service account. This is similar to setting up proxy accounts for SQL Agent jobs.
There is a connect item discussing (well discussing...) exactly this issue, so it appears to be a bug.
Text 'xp_cmdshell' causes transport-level error: semaphore timeout period has expired from remote system
It appears that the string causes problems, not only in comments but also anywhere it's used as an exact string so even
Since using sp_send_dbmail is an option here, I don't see why you need to export anything given that sp_send_dbmail can run a query and include the results, either in the body or as an attachment. I would first try to make use of the @query, @attach_query_result_as_file, @query_attachment_filename, @query_result_header, @query_result_width, @...
The problem is, 1> has a specific meaning. It means "redirect standard output", whereas 2> would be "redirect standard error". > by itself is short-hand for 1>.
You can try:
SET @CMD = 'ECHO>'+@Vercheck + ' ' + @Ver
Since you can perform redirection before the rest of the arguments to echo.
Instead of using xp_cmdshell, which comes with a lot of security risks, you could run something like this from the command prompt:
osql >output_file.txt -S myServer\myInstance -E -Q "PRINT @@VERSION"
The -S switch denotes the name of the server and instance, -E means Windows authentication (you could instead use -U and -P for ...
There are several problems here:
The main issue is that the CMD shell executes each line as it comes in via a return; it does not wait for an "end of command" indicator such as ;, nor does it attempt to figure it out via parsing (like SQL does upon a batch being submitted) since there is no way to know if a return ends a "command" or not. Typically you can ...
The problem is with your variable declaration varchar.
The command_string is varchar(8000) or nvarchar(4000)
so define your variable along with proper length to avoid string truncation
couple of things as well (since I dont know Python).
Make sure you print or ...
The issue may be the line feeds in the command you're trying to execute. Consider the difference between the following cases.
-- case 1
execute xp_cmdshell N'powershell.exe -noexit -c "echo hello"';
-- case 2
execute xp_cmdshell N'powershell.exe -noexit -c "
To execute multi-line powershell scripts from xp_cmdshell, try saving the entire ...
This can be accomplished somewhat easily by using a "loop back" Linked Server that has the 'remote proc transaction promotion' property set to false, which avoids the Cannot perform a shrinkfile operation inside a user transaction error by side-stepping the implicit transaction started by the INSERT...EXEC operation. The Linked Server will otherwise only ...
You can use a SQL Server login.
The following procedure is explained in MSDN:
To allow non-administrators to use xp_cmdshell, and allow SQL Server
to create child processes with the security token of a less-privileged
account, follow these steps:
Create and customize a Windows local user account or a domain account with the least privileges ...
Since no one else has mentioned this as a possibility, you can do this all directly via TSQL commands.
First, you can directly write out to a file via either a linked server (if the file will have a static path) or via OPENROWSET or OPENDATASOURCE functions if the files are more dynamic in nature. Details on how to do this for excel files can be found here:...
Highly recommended to use awesome dbatools Powershell module.
Just install this module:
Then use Get-DbaDatabaseSpace function:
# Get Db Free Space AND write it to table
Get-DbaDatabaseSpace -SqlInstance $instance | Out-GridView
Get-DbaDatabaseSpace -SqlInstance $instance -IncludeSystemDB | Out-DbaDataTable | Write-DbaDataTable -...
I'm not sure if this will give you exactly what you want, but you could try this:
DECLARE @cmd VARCHAR(2000)
SET @cmd = 'DIR "\\SHARENAME"'
EXEC xp_cmdshell @cmd
The last line would give you the freespace
04/04/2016 05:39 AM 15,872 TrackSqlLogins_0_131042360126710000.xel
08/15/2014 07:01 AM 449 ...
Operating system error 5(Access is denied.)
This error is somewhat cryptic, but it is helpful. This error is coming from Windows when it tries to write to file. Windows then passes the error message along back to SQL Server, who dutifully tells you exactly what it was told--unfortunately without a better explanation.
"Operating system error 5" generally ...
does this example show a serious security problem, or am I misunderstanding something?
You are misunderstanding what is actually going on here.
the database owner is changed from sa to another low privileged login, then the user stored proc is no longer able to call xp_cmdshell.
That is true in this case, but that is merely due to how you did (or more ...
You should definitely find a better way than doing DML through a function. You don't need to attach this process to a SELECT statement. You can just dump the results of the query to a temp table and use a CURSOR to iterate over that (just like you are doing in this function), calling a stored procedure to do what you are currently doing in this function.
If we ignore that UNION would remove duplicates (which seems valid to ignore in the case given in the question because 1 can't clash with a DOMAIN\UserName string).
EXEC master.sys.xp_cmdshell N'echo 1 & whoami'
No, it is not very safe if you get untrusted input into the database (i.e. if it is on a web server or such). You basically hand the user a really dangerous tool if you have any SQL injection vulnerability. I would not run that risk on public-facing servers.
Internal servers may be another thing, but internal attacks are often overlooked, so probably "we" ...
Without discounting anything stated in @til_b & @unclefredo answer.
I would like to say something facts about 'xp_cmdshell'. Before enable the 'xp_cmdshell' you must know the some facts about xp_cmdshell.
The facts with xp_cmdshell on SQL Server 2005 or newer, including SQL Server 2016.
xp_cmdshell is disabled by default on install.
Only those ...
The below process is how I managed to save the results of dbcc shrinkfile into a table
enable the use of xp_cmdshell
get a login or proxy to run the script
so we need the following script:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
This will depend on your regional settings, language, and potentially other settings.
WHERE TRY_CONVERT(date, LEFT(strData,10)) = CONVERT(date, GETDATE());
To avoid anything else from entering the #temp table in the first place, you can use forfiles, which allows you to specify a pattern (eliminating all the extra junk dir adds), specify a date (in ...
xp_cmdshell can be executed without direct execute permissions in an sa-owned database if the cross database ownership chaining at the server level, DB_CHAINING database option for the testdb database, or EXECUTE AS OWNER is specified in the proc. These are all non-default configurations and are off by default. Importantly, one should not allow non-sysadmin ...
However, it expects a password.
Correct, the password is encrypted and stored in the master database. When the account needs to be used, the password is decrypted and passed in.
Is it possible to use a gMSA as the proxy account for xp_cmdshell in SQL Server 2014?
I don't believe so, and part of the reason why I included the quote about the password ...
If I understand your question correctly, let me try providing an alternate solution.
You could write a quick powershell script to automate the whole process -
-reading source .sql files
In a loop -
-replacing 'Alerts' word with A (B and C).
-run those files calling sqlcmd within the powershell script
Create a bat file that executes the Windows FTP process which reads in your cmdftp_put.txt file as input commands.
An example of the bat file (FtpBackupFile) might look like this:
Then use the following TSQL
exec xp_cmdshell 'c:\FtpBackupFile.bat'
The Sql Server service account will need Windows ...