Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Permissions problem running bcp Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 12:46 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:56 AM
Points: 243, Visits: 799
Hi all,

I have a s/p which runs bcp and creates a CSV export file. Works fine in development (my rights) but not for any other user. To run bcp, I need to use sp_configure to turn on 'configure advanced options', then xp_cmdshell to run bcp. Both of these options are OFF by default.

SO: I turn on the advanced options, turn on xm_cdmshell, run my code, turn off xp_cmdshell, then turn off advanced options.

It works great for me but other users get permissions errors trying to run the system stored procs. They can run the s/p in my local db, but not the ones that live in master db. I've been playing with GRANT and EXECUTE AS but have no luck yet with either one. Has anyone ever done this?

Thanks!


Here's my [edited] code, a few things left out for clarity:



ALTER PROCEDURE [dbo].[aMyAppCreateUploadCSV]

AS

DECLARE @SQL varchar(255)
DECLARE @FileName varchar(50)
DECLARE @DestFolder varchar(100)

-- Turn on Advanced security options
EXECUTE master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
-- make sure xp_cmdshell is turned on and reconfigure with new value
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

SELECT @FileName = 'Upload_TEST.csv';
SELECT @DestFolder = '\\myserver\user\UploadFiles\';

SET @SQL = 'bcp "exec [MyDB].[dbo].aExtractDataCSV" queryout "' + @destFolder + @fileName + '" -t \, -T -c'

EXECUTE master.dbo.xp_cmdshell @SQL

-- turn off xp_cmdshell and reconfigure with new value
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
-- Turn off Advanced security options
EXECUTE master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE



Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1454689
Posted Monday, May 20, 2013 1:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
here's an example of EXECUTE AS with a super user:

now even as a super user, if you are trying to touch network shares or special folders , you might still get permissions problems , depending on the acocunt being used to run the SQL service...that is what has to have file permissions.
GO
--create our super user
CREATE LOGIN [superman] WITH PASSWORD=N'NotARealPassword',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;
GO
--make our special user a sysadmin
EXEC master..sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin';
GO
--noone will ever login with this, it's used for EXECUTE AS, so disable the login.
ALTER LOGIN [superman] DISABLE;
GO
USE [SandBox];
GO
CREATE USER [superman] FOR LOGIN [superman];
GO
USE [SandBox];
GO
EXEC sp_addrolemember N'db_owner', N'superman';
GO
--now create our procedure that runs under special priviledges instead of as the caller.
--the EXECUTE AS must be a user in the database...not a login
CREATE PROCEDURE TestCmdShell
WITH EXECUTE AS 'superman'
AS
BEGIN
SET NOCOUNT ON
create table #Files (
FName varchar(1000));

insert into #Files (FName)
exec master..xp_cmdshell 'dir c:\*.txt /b';

select * from #Files;
END
GO


EXECUTE AS USER='superman';
DECLARE @Results table(
ID int identity(1,1) NOT NULL,
TheOutput varchar(1000))

insert into @Results (TheOutput)
exec master..xp_cmdshell 'whoami' --nt authority\system for example

insert into @Results (TheOutput)
exec master..xp_cmdshell 'cd %userprofile%' --NULL because nt authority\system is not a user...command fails.

select * from @Results

if the above returns blanks or nt authority\system, then you need to modify the startup account


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1454697
Posted Monday, May 20, 2013 5:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 36,711, Visits: 31,161
Lowell (5/20/2013)
if the above returns blanks or nt authority\system, then you need to modify the startup account



How does the startup account need to be modified because I'm getting the following error...
Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1454773
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse