I have a stored procedure that runs xp_cmdshell with a user with sysadmin right.(it was created by someone else long time ago). For security purposes I need to remove the sysadmin right from this user. So I created a proxy account and gave execute privilege to this user on the xp_cmdshell (practically followed the steps how to run xp_cmdshell with a non sysadmin user ). But when I run the stored procedure I am getting the below error: > error number 15466 severity 16 errorline 1 An error occurred during decryption. I use SQL Server 2008 What could be the problem?

I need to use xp_cmdshell in one of SQL job.So is it safe to reconfigure xp_cmdshell to 1 or can i set value 1 in the starting of SQL statements and set it back to 0 in the end of SQL Statement ?

So far I've this code CREATE TABLE #tmp (strData VARCHAR(1000)) INSERT INTO #tmp EXEC xp_cmdshell 'dir C:\Users\Giba\Desktop\Folder\cmdshell1\*.txt' SELECT * FROM #tmp --WHERE strData LIKE '09/25/2018' DROP TABLE #tmp but I want to select only date of the .txt files and compare them with current date. Can someone help to select only the date?

I have to copy latest backup from a network path and bring that to my SQL server physical drive for restore, I have to do that using agent job. This is what i am doing. Created a SQL server agent job 1) XCOPY source *.bak /Y destination 2) Ren *bak mydbbackup.bak Questions: *What mistake am i doing here ?* *Are there any xcopy **/**commands which can copy the latest files only and ignore old files ? Is there a way i can do that using another SQLCMD command besides creating a stored procedure* Thanks, learner

After running Windows Server updates (including server restart), SQL Server experienced problems executing XP_ commands. These are stuck forever without any response. The only solution I have found so far is to restart the SQL Server services, which is not appropriate in a productive environment. The operating system is Windows Server 2012 R2 Standard and the database engine is SQL Server 2014 Standard Is it possible that Windows Server updates are blocking these extended stored procedures from running? There are no errors seen in the SQL Server logs related to the problem. I have checked the Windows Event Viewer events and have not found any indication of what may be happening either. Until now, I have carried out the following actions without positive results: 1. I have updated SQL Server 2014 to the latest patch available today (5029185 Security update for SQL Server 2014 SP3 CU4: October 10, 2023). 2. I have installed a new instance of SQL Server 2014 on the same server, with the latest patches, but the problem is still the same. This makes me think that the problem is with the operating system. As background, a SQL Server 2017 instance is installed on this same server, which has not been affected. Apparently the problem only affects SQL Server 2014. Thank you all in advance for your answers or suggestions.

Dears , i am trying to make the mapped drive visible to SQL server and as a security requirement, I need to use user name and encrypted password because this will be saved on the logs if it is plain text. EXEC xp_cmdshell 'NET USE F: \\DefinitelyNotMyLaptop\SQLBackups/ user:SqlUser P@$$w0rd' i tried to declare a variable and save the .txt password on it but it did not work any ideas? - SQL server enterprise edition ,2019

Using xp_cmdshell can be quite helpful and sometimes possibly the only answer to some scenarios. I've read some posts on the internet that enabling xp_cmdshell might jeopardize the security of the database/server. My question is that, is there anything we can do to reduce the risk? For example can we set some restrictions/applying users role, etc providing safeguards to mitigate the risk? Thanks.

I want to create a CLR assembly, and here's my code:

use [master]
go

exec sp_configure 'show advanced options', 1
go

reconfigure
go

exec sp_configure 'xp_cmdshell', 1
go

reconfigure
go

execute sp_configure 'clr enabled', 1
go

reconfigure
go

execute sp_configure 'clr strict security', 0
go

reconfigure
go

declare @path varchar(256) = '';
declare @table table
(
	Value varchar(256)
)
insert into @table execute xp_cmdshell 'echo %CompanyProjectsRoot%'
set @path = 
(
	select top 1 Value 
	from @table
)
set @path = @path + '\Framework\Packages\Company.ClrIntegration.dll'
create assembly CompanyClrIntegration from @path
go

And I receive this error: > CREATE ASSEMBLY failed because it could not read from the physical file '%CompanyProjectsRoot%\Framework\Packages\Company.ClrIntegration.dll': 50(The request is not supported.). But if I change the path to absolute path C:\Copmany\Framework\Packages\Company.ClrIntegration.dll it works. And I know that %CompanyProjectsRoot% is already defined for the entire machine using this command: setx CompanyProjectsRoot C:\Company /m And I have also restarted SQL Server Management Studio. But still it does not expand environment variable. How can I fix this?

I've included the entire code

USE [JCIHistorianADX2New]
GO
/****** Object:  StoredProcedure [dbo].[GetADX2PointsDyn]    Script Date: 10/18/2021 5:45:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- =============================================
-- Author:		FLG
-- Create date: 10/15/2021
-- Description:	Process ADX2 Points
-- =============================================
ALTER PROCEDURE [dbo].[GetADX2PointsDyn] 
	-- Add the parameters for the stored procedure here
	@dSDate datetime = '2021/01/01 00:00:00', 
	@dEDate datetime = '2021/01/01 23:45:00'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Create Table #Points
	(
	EMSPointName varchar(100) NOT Null
	);


BULK INSERT #Points
FROM 'C:\Trend Point Map 101521.csv'
WITH
(
    FIRSTROW = 2, -- as 1st one is header
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)
    -- Insert statements for procedure here

	Alter Table #Points
	Add ID int Identity(1,1)

	DECLARE
		@Init int= 1,
		@NumRows int
		
	SELECT @NumRows= COUNT(*) FROM #Points WHERE ID= @Init
	WHILE @Init= @dSDate and UTCDateTime <= @dEDate

			AND Exists(
			Select t.EMSPointName
			from #Points T
			Where t.EMSPointName = [dbo].RawAnalog.PointName)'

			Print 'SQL'
			Print @SQL

	

			set @cmd = N'master.dbo.xp_cmdshell ''BCP ' + '"'  + @SQL +'"' + ' queryout  E:\Honeywell\GSA\Test\ADX2Test.txt -c -t, -S (local) -T '''
			--set @cmd = 'BCP + @SQL + QUERYOUT + @path + "ADX2Test.txt" -c -t, -S (local) -T'

			Print 'CMD'
			Print @cmd

			Exec @cmd
			

			SET @Init= @Init + 1
		END
	
	SELECT @dSDate, @dEDate
END

We are small IT department and we are changing how our infrastructure works. We are trying to make it more secure, manageable and transparent for auditing. We are implementing a domain for our servers and possibly in the future for our workstations. --- Disclaimer: *This questions won't be very strict but rather touch many different problems like Linked Servers or SSIS. I decided that there is no point of splitting this question into separate threads as it all boils down to a single concept of managing database infrastructure and permissions. Each of this separate concepts need to work in sync with everything else and this question is about that whole concept. **So please bear with me. I will try to formulate specific questions at the end but this is hard for me as this is unknown territory for me and I could miss some crucial topic.*** If you think I should formulate my question in different way please let me know. --- Currently our concept looks like this (a little simplified for the sake of this question): Green arrows show where we would modify access. The rest is pretty much static. We have multiple computers hosting SQL Server instances. Each instance can have multiple databases. For the most part each computer/SQL Server is "encapsulated" and we can assume that we do not split permissions across each database but rather treat whole SQL instance with all its database as single entity. For each SQL instance we have couple of permission levels (at the moment we distinguish 4 - *Read, Write, Developer, Admin*). Each of this permission levels is a database login with windows authentication based on domain groups. We also have couple of external companies that are deploying and managing many different applications and services living on our servers. We create domain group for each company and then add them to appropriate groups with SQL login attached. Our goal is to manage access to our servers from a domain. This approach gives us one centralized point of control for all SQL Servers and more transparency, as we basically create logins once and ideally never touch them again by only working from AD level. **But the world isn't so clean and simple.** We had all that laid out but then Linked Server, SQL Agent, SSIS, xp_cmdshell etc. came into picture. --- First lets talk about Linked Server. At the beginning we thought that we could just use "*login's current security context*". People or applications would login with their standard domain credentials and linked server would pass that to the next server. So we could manage from the AD if particular user should have access to that particular remote server or not. Below you can see blue arrows that represent that domain access on both servers. But we quickly hit the **double hop problem** where credentials are not passed from one server to another and the whole approach would not work for complex multi server queries. So the question is what should we do about Linked Servers? Should we just use SQL authentication? This mean in Server 1 we create an SQL login that correspond to a Linked Server in Server 2 etc.? This is potential problem as people could access linked server even when they should not have access to that remote server. And it is more complicated than using domain groups only. Maybe there is a way of integrating this with concept of managing everything from the domain? I looked into Kerberos and it seems like it might be a solution to a double hop problem, but I'm not sure if our small team can handle such rabbit hole as Kerberos. --- Next there is a problem with SQL Agent and similarly with SSIS. Who should be able to run the jobs? Who should have access to what? Should there be a domain account for SSIS that would have corresponding login on every server inside SSIS? Or we don't need that and we could get by only by assigning database-level roles in SSISDB? Or are there any gotchas that I'm missing? Like dedicated OS user for SSIS or SQL Agent or proxy? --- In case of special command xp_cmdshell I've read that we need proxy account for it to work for non-sysadmin account. What is recommended way of approaching this? Each server should have its own domain account for running this command? Is it good idea to use domain user for for everything? SQL Server service account, proxy account, SQL Agent and Linked Servers? What do you think? I know this big question but I'm stuck and not sure what to do next.

I have created a job to test executing a CmdExec step. The job step is simply executing a batch file with dir as the command. The job is executing as the SQL Server service account. This account is both sysadmin of SQL Server and Administrator in Windows. The login also has been granted exec to xp_cmdshell, in master. In Surface Area Configuration, XPCmdShellEnabled = True. If I run the command from the query window it executes as expected. Question: why am I still getting the job step error? This is plain vanilla install of SQL Server 2008 R2 with SP2 installed. Nothing fancy going on here. Note: I know how horribly insecure this is, but this is where it has come to in order for me to chip away at this and try and resolve this error. Best I can tell, this just can’t be made to work and BOL makes it sound so easy.

I'm doing some testing and I want to execute a PowerShell script through xp_cmdshell. Below is the PowerShell script (c:\temp\sqltotext.ps1). echo "haha" | Out-File "c:\temp\haha.txt" Then I execute the script through xp_cmdshell like this. DECLARE @result INT; EXEC @result = sys.xp_cmdshell N'powershell.exe -Command "Start-Process powershell.exe -Verb RunAs ''-NoProfile -ExecutionPolicy Bypass -File c:\temp\sqltotext.ps1''"'; IF (@result = 0) PRINT 'Success'; ELSE PRINT 'Failure'; It runs successfully and I can find the file c:\temp\haha.txt with the content haha. However, when I change the content of c:\temp\sqltotext.ps1 to: echo Process CurrentUser LocalMachine | %{Set-ExecutionPolicy bypass -Force -Scope $_} and execute the same aforementioned TSQL command, the TSQL reports success but I didn't get the expected result (the execution policy in all scopes were NOT changed). When I execute the PowerShell script manually (in a PowerShell console and type c:\temp>.\sqltotext.ps1), it works as expected (the execution policy in all scopes were changed). Why this happens? I did some investigation though. EXEC xp_cmdshell 'whoami.exe' reports I'm running with nt service\mssqlserver. I also manually added nt service\mssqlserver into the administrators local user group. By using process explorer, I can confirm the PowerShell session indeed was started and all related processes have admin permission (Integrity = High). The cmd.exe command line looks like this: When I execute this command manually, it can change the execution policy without any issue (I changed all execution policy back to the original values after this). The PowerShell process command line looks like this: When I execute the PowerShell process command line manually, I can change the execution policies too. I don't have any idea why when I run the command through xp_cmdshell, it doesn't change anything. BTW, I know there are multiple ways to write the command. I'm just talking about the technical skills here, so please don't suggest me changing command syntax etc.

I'm having a hard time figuring out on how can I export my selected table in a network path that requires login credentials.

'bcp "select ''StudentName'' UNION ALL SELECT StudentName AS StudentName from [School].[dbo].[ClassRoom]" "queryout \\IPAddress\d$\Log\StudentLog_'+@fileTimeStamp+'_01'+'.'+@fileExtension+'" -c -t, -T -S' + @@servername

Running this gives me an error of >SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file NULL Where should i put in the script the authentication for my network path?

I have a job to delete past 15 days folder in SQL Server. The job run successfully and delete the folder in local SQL Server. But it runs successfully and does not delete the folder in another SQL Server which is accessed by remote desktop connection. Please suggest how to solve it. Is it permission problem? The following is the code I use { --Script to enable the XP_CMDSHELL -- 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 DECLARE @path VARCHAR(256) -- path for backup files DECLARE @cmd VARCHAR(8000) DECLARE @folderName VARCHAR(256) -- filename for backup SET @folderName = + GETDATE()-15 --(CONVERT(varchar(10), GETDATE()-15, 'yyyy_mm_dd')) -- 15 days back date folder name SET @folderName = CONVERT(varchar(4),YEAR(@folderName)) + '_' + Right('00' + CONVERT(varchar(2),MONTH(@folderName)),2) + '_' + Right('00' + CONVERT(varchar(2),DAY(@folderName)),2) SET @path = 'D:\FTP-Snapshots\' + @folderName -- Folder path SET @cmd = 'RD ' + @path + ' /S /Q' -- Delete Files and the Folder IF all files deletions were successful. EXEC master..xp_cmdshell @cmd }

I have an app on 10 or so servers that runs a few xp_cmdshell statements against MSSQL 2008. It works fine on all the servers except for one. To make things worse, I can run all the commands in SQL Management Studio, but in the app, they don't work. I even made a temporary app to test and it works fine! But in the deployed app, I get a simple SQL error "Access Denied". I have narrowed it down to the connection string, if I include the application name Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};Application Name=TheGroovyApp It throws access denied only when calling xp_cmdshell, normal SQL statements works fine. But if I remove the application name Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3} It works fine for both normal SQL statements and calls to xp_cmdshell. The strange thing is, its only happening on one of the ten servers. The only difference being that the server has SP1 and the others do not. @@VERSION returns Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (VM) I am thinking that there is some sort of authentication that can be granted to applications, but I cant seem to find anything. I can replicate it in SQL Managment Studio by adding Application Name=TheGroovyApp to the Additional Connection Parameters tab on the Connect to Database Engine dialog when you create a new query or change its connection. The simple test statement I use is EXEC master..xp_cmdshell 'DIR F:\SomeDirectory' If anyone could shed some light on what is happening it would be much appreciated. **EDIT:** OK after a bit more investigation its even more confusing. If I set the Application Name to the following which is the default for .Net connections, it works fine. Application Name=".Net SqlClient Data Provider" I can run xp_subdirs without any problems no matter which settings I use EXEC master..xp_subdirs 'F:\SomeDirectory' Now this is where it gets really weird. The first two fail, but the last one succeeds with the application name being set to my application name. But only if its xp_cmdshell that get called, xp_subdirs works with all three. **With Application Name in connection set** EXEC master..xp_cmdshell 'DIR F:\SomeDirectory' - Fails master..xp_cmdshell 'DIR F:\SomeDirectory' - Fails xp_cmdshell 'DIR F:\SomeDirectory' - Works EXEC master..xp_subdirs 'F:\SomeDirectory' - Works master..xp_subdirs 'F:\SomeDirectory' - Works xp_subdirs 'F:\SomeDirectory' - Works **With Application Name not set in connection** EXEC master..xp_cmdshell 'DIR F:\SomeDirectory' - Works master..xp_cmdshell 'DIR F:\SomeDirectory' - Works xp_cmdshell 'DIR F:\SomeDirectory' - Works EXEC master..xp_subdirs 'F:\SomeDirectory' - Works master..xp_subdirs 'F:\SomeDirectory' - Works xp_subdirs 'F:\SomeDirectory' - Works **The error returned in the query messages area in SQLMS when it fails** Msg 10011, Level 16, State 1, Line 1 Access denied. This error message is only happening on the one server, I cannot replicate this on any others.

I am trying to use xp_cmdshell to launch a python script with a list as its input but for some reason its only passing the first character of the string to command line. The result I get from the code below is "'D' is not recognized as an internal or external command, operable program or batch file.". If I add the START command before the path it just replaces 'D' with 'S'. This particular row I'm trying to test with concatenates to 241 characters so I'm not bumping up against the varchar limits for xp_cmdshell. Using MSSQL 2008R2 on Windows Server 2008R2. DECLARE @inspection varchar SELECT @inspection = 'D:/Python27/ArcGISx6410.1/python.exe D:/GIS-DATA/Scripts/whydrant_update_01.py [''' + COALESCE(FACILITYID, '') + ''',''' + COALESCE(MAKER, '') + ''',''' + COALESCE(HYDRCOND, '') + ''',''' + COALESCE(BASECOL, '') + ''',''' + COALESCE(CAPCOL, '') + ''',''' + COALESCE(STEAMSIZE, '') + ''',''' + COALESCE(DISSIZE, '') + ''',''' + COALESCE(CAST(FLOW AS varchar), '') + ''',''' + COALESCE(CAST(PRESSURE AS varchar), '') + ''',''' + COALESCE(VALVELOC, '') + ''',''' + COALESCE(ACTIVE, '') + ''',''' + COALESCE(OPERABLE, '') + ''',''' + COALESCE(OPERHARD, '') + ''',''' + COALESCE(CORRODED, '') + ''',''' + COALESCE(CLEARING, '') + ''',''' + COALESCE(PAINTING, '') + ''',''' + COALESCE(MARKER, '') + ''',''' + COALESCE(NOTE, '') + ''',''' + COALESCE(CAST(created_date AS varchar), '') + ''',''' + COALESCE(USER_, '') + ''']' FROM sde.sde.a1288 WHERE FACILITYID = '2294' EXEC xp_cmdshell @inspection

I am using msdb.sp_senddbmail procedure to send emails from MS SQL Server but now I need to move to OAUTH2.

I tried to implement separate application and call it from procedures using xp_cmdshell but
there are some limitations like argument character number limit and I can't pass HTML from sql to app easy so I am still trying to find best solution for this.

Is it possible to set somehow sp_senddbmail to use OAUTH2?

2 days ago I created a function that needs INSERT and UPDATE, and I enabled XP_CMDShell and do it by running a script. Afterwards, SQL Server works very slow for SELECT commands. Even very simple SELECT statement that runs after INSERT via separated command. I tested this behaviour on other databases and it giae same result that SELECT runs after some minutes. Also, I tested it on 2 other hosts with SQL Server 2014 and the result was the same. Function that I created to get value of it within another select statement: ALTER FUNCTION [Prg].[intCheckDelayedProcessProgram] ( @SalesOrderProductID INT, @MainProductTreeID INT, @ProductTreeID INT, @ProcessId INT, @additionalDays INT = 2, @currentDateReverceString VARCHAR(10) = NULL ) RETURNS INT AS BEGIN --declare @SalesOrderProductID INT = 40957, -- @MainProductTreeID INT = 93758, -- @ProductTreeID INT = 93758, -- @ProcessId INT = 4472, -- @additionalDays INT = 2, -- @currentDateReverceString VARCHAR(10) = null -- '30/09/1398' DECLARE @ProduceDailyProgramProductTree_Id INT, @ProgramQuantity INT, @startDate JalaliDate, @CurrentDate JalaliDate, @lastDate JalaliDate, @additionalDate JalaliDate, @holiDaysCount INT; IF(@currentDateReverceString IS NULL OR @currentDateReverceString = '') SET @CurrentDate = dbo.GetCurrentJalaliDate(); ELSE SET @CurrentDate = Gnr.RevercePersianDate(@currentDateReverceString); IF (@additionalDays IS NULL) SET @additionalDays = 2; DECLARE @allDelayedItemsCount INT = 0; BEGIN DECLARE @sql NVARCHAR(4000), @cmd VARCHAR(4000); DECLARE cursor_pdppt CURSOR FOR SELECT pdppt.ID, pdppt.ProgramQuantity, pdppt.[Date] FROM Prg.ProduceDailyProgramProductTree pdppt WHERE pdppt.SalesOrderProductID = @SalesOrderProductID AND pdppt.MainProductTreeID = @MainProductTreeID AND pdppt.ProductTreeID = @ProductTreeID AND pdppt.Process = @ProcessId AND ISNULL(pdppt.IsDelayed, 0) = 0 OPEN cursor_pdppt; FETCH NEXT FROM cursor_pdppt INTO @ProduceDailyProgramProductTree_Id, @ProgramQuantity, @startDate; WHILE @@FETCH_STATUS = 0 BEGIN SET @lastDate = Gnr.RevercePersianDate([Prg].[intGetLastDateForDelayedProcessProgram](@startDate, @additionalDays, @CurrentDate, 1)); IF (@CurrentDate.GetDate() > @lastDate.GetDate()) BEGIN IF ((SELECT COUNT(*) FROM Prg.ProduceDailyOperation pdo WHERE pdo.ProduceDailyProgramProductTreeID = @ProduceDailyProgramProductTree_Id AND pdo.Process = @ProcessId AND pdo.[Date].GetDate() > @lastDate.GetDate()) = 0) BEGIN SET @allDelayedItemsCount = @allDelayedItemsCount + @ProgramQuantity; END ELSE BEGIN SET @allDelayedItemsCount = @allDelayedItemsCount + (@ProgramQuantity - (SELECT SUM(pdo.ProducedQuantity) FROM Prg.ProduceDailyOperation pdo WHERE pdo.ProduceDailyProgramProductTreeID = @ProduceDailyProgramProductTree_Id AND pdo.Process = @ProcessId AND pdo.[Date].GetDate() 0) BEGIN IF (EXISTS(SELECT 1 FROM Prg.ProduceDailyProgramProductTreeDelayed pdppt WHERE pdppt.SalesOrderProductID = @SalesOrderProductID AND pdppt.MainProductTreeID = @MainProductTreeID AND pdppt.ProductTreeID = @ProductTreeID AND pdppt.Process = @ProcessId)) BEGIN SELECT @allDelayedItemsCount = @allDelayedItemsCount + (SELECT pdpptd.DelayedQuantity FROM Prg.ProduceDailyProgramProductTreeDelayed pdpptd WHERE pdpptd.SalesOrderProductID = @SalesOrderProductID AND pdpptd.MainProductTreeID = @MainProductTreeID AND pdpptd.ProductTreeID = @ProductTreeID AND pdpptd.Process = @ProcessId AND ISNULL(pdpptd.Active, 0) = 1 AND ISNULL(pdpptd.IsDeleted, 0) = 0); SELECT @sql = 'UPDATE [Prg].[ProduceDailyProgramProductTreeDelayed] SET DelayedQuantity = ' + CONVERT(VARCHAR(10), @allDelayedItemsCount) + 'WHERE SalesOrderProductID = ' + CONVERT(VARCHAR(10), @SalesOrderProductID) + 'AND MainProductTreeID = ' + CONVERT(VARCHAR(10), @MainProductTreeID) + 'AND ProductTreeID = ' + CONVERT(VARCHAR(10), @ProductTreeID) + 'AND Process = ' + CONVERT(VARCHAR(10), @ProcessId) +';'; END ELSE BEGIN SELECT @sql = 'INSERT INTO [Prg].[ProduceDailyProgramProductTreeDelayed] (SalesOrderProductID, MainProductTreeID, Process, ProductTreeID, DelayedQuantity, Active, IsDeleted) VALUES (' + CONVERT(VARCHAR(10), @SalesOrderProductID) + ', ' + CONVERT(VARCHAR(10), @MainProductTreeID) + ', ' + CONVERT(VARCHAR(10), @ProcessId) + ', ' + CONVERT(VARCHAR(10), @ProductTreeID) + ', ' + CONVERT(VARCHAR(10), @allDelayedItemsCount) + ', ''1'', ''0'')'; END; SELECT @cmd = 'sqlcmd -S ' + @@SERVERNAME + ' -d ' + DB_NAME() + ' -Q "' + @sql + '"' EXEC MASTER..XP_CMDSHELL @cmd , 'no_output' END ELSE IF(EXISTS(SELECT 1 FROM Prg.ProduceDailyProgramProductTreeDelayed pdpptd WHERE pdpptd.SalesOrderProductID = @SalesOrderProductID AND pdpptd.MainProductTreeID = @MainProductTreeID AND pdpptd.ProductTreeID = @ProductTreeID AND pdpptd.Process = @ProcessId AND ISNULL(pdpptd.Active, 0) = 1 AND ISNULL(pdpptd.IsDeleted, 0) = 0)) BEGIN SELECT @allDelayedItemsCount = (SELECT pdpptd.DelayedQuantity FROM Prg.ProduceDailyProgramProductTreeDelayed pdpptd WHERE pdpptd.SalesOrderProductID = @SalesOrderProductID AND pdpptd.MainProductTreeID = @MainProductTreeID AND pdpptd.ProductTreeID = @ProductTreeID AND pdpptd.Process = @ProcessId AND ISNULL(pdpptd.Active, 0) = 1 AND ISNULL(pdpptd.IsDeleted, 0) = 0); END; END; RETURN @allDelayedItemsCount; END JalaliDate is user defined type to hold persian date time by Assembly. Gnr.RevercePersianDate function to convert string persian date to JalaliDate. [Prg].[intGetLastDateForDelayedProcessProgram] other function to calculate specified date by Holidays that I think dose not issue. **UPDATE 1** I test function codes by pass parameter values by inline declare and set test values. Then, user Sql Server Profiler to check script of function, that see scripts stops on this line SET @CurrentDate = dbo.GetCurrentJalaliDate() that use function of Assembly. Execution running without any error and not responding!! Note: I test SELECT dbo.GetCurrentJalaliDate() that responding very fast without error! **UPDATE 2** I call function [Prg].[intCheckDelayedProcessProgram] within SP and inside a complex SELECT statement, that can't call SP in select, so just solution for me was define function. Code run in local server that is together with all data and other SP and functions. If there is alternative for CURSOR help me to rewrite function with it.

I am trying to understand details of wrapping xp_cmdshell functionality in user defined stored procs, so that other users can just be given execute permission to the stored procs rather than xp_cmdshell. The steps in this scenario are: 1. xp_cmdshell is already enabled and a xp_cmdshell proxy has been created 2. A user with db_owner membership creates a stored proc WITH EXECUTE AS OWNER which calls xp_cmdshell 3. The user executes the stored proc and therefore executes arbitrary shell code This is unexpected to me. I would not expect a user with only db_owner to be able to achieve this. (Obviously assuming xp_cmdshell has already been enabled by a sysadmin.) When 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. USE MASTER; CREATE DATABASE testdb; CREATE LOGIN testuser WITH PASSWORD = 'password', CHECK_POLICY=OFF; CREATE LOGIN dummyuser WITH PASSWORD = 'password', CHECK_POLICY=OFF; SELECT * from sys.credentials WHERE NAME LIKE '%cmdshell%'; -- returned: 101 ##xp_cmdshell_proxy_account## ..... USE testdb; EXEC sp_changedbowner 'sa'; CREATE USER testuser FOR LOGIN testuser; ALTER ROLE db_owner ADD MEMBER testuser; EXECUTE AS LOGIN = 'testuser'; GO CREATE PROCEDURE [dbo].[testproc] WITH EXECUTE AS OWNER AS SELECT SUSER_NAME() as [SUSER_NAME()], USER_NAME() as [USER_NAME()]; exec xp_cmdshell 'echo %time%'; GO SELECT SUSER_NAME() as [SUSER_NAME()], USER_NAME() as [USER_NAME()]; EXEC dbo.testproc; -- returned: sa dbo proving that the call to xp_cmdshell has succeeded EXEC xp_cmdshell 'echo %time%'; -- returned: The EXECUTE permission was denied on the object 'xp_cmdshell' REVERT EXEC sp_changedbowner 'dummyuser'; EXECUTE AS LOGIN = 'testuser'; EXEC dbo.testproc; -- returned: The EXECUTE permission was denied on the object 'xp_cmdshell' -- proving that the sysadmin role of the database owner is relevent REVERT Note that I have not granted execute xp_cmdshell permission to any particular user. I thought that enabling xp_cmdshell was ok if care was taken to only grant execute xp_cmdshell permission carefully, but my example seems to show otherwise. Since a sysadmin is often a database owner, does this example show a serious security problem, or am I misunderstanding something?

I have a job which runs every 30 minute and takes t-log backup of the database suing Ola's script. Every Thursday and Friday at 22:45 I get an error: 11/07/2019 22:45:00,Backup,Unknown,Error: 3041 Severity: 16 State: 1. 11/07/2019 22:45:00,spid60,Unknown,BackupDiskFile::CreateMedia: Backup device 'B:\SDSOFT-DB\EXPRESS\LOG\SDSOFT-DB_EXPRESS_LOG_20191107_224500.trn' failed to create. Operating system error 5(Access is denied.). 11/07/2019 22:45:00,spid60,Unknown,Error: 18204 Severity: 16 State: 1. I asked sysadmin to check all settings regarding NAS storage and he said that everything ok and he doesn't know what is the cause of this problem. From my side, I mapped NAS storage network path into 'B' using xp_cmdshell(SQL SERVER 2014) by the help of the following command: EXEC xp_cmdshell 'net use B: \\192.*.*.*\DBbackups /user: /persistent:yes' I would like to point out again that I get no error during the rest of the time, only on Thursday and Friday at 22:45. Maybe someone had faced with this issue before and help me to tackle this problem.