SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Permissions problem running bcp


Permissions problem running bcp

Author
Message
Sigerson
Sigerson
Say Hey Kid
Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)

Group: General Forum Members
Points: 683 Visits: 1232
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28494 Visits: 39972
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87468 Visits: 41116
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search