Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 14, 2016 9:23 AM
Points: 537, Visits: 1,229
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 @ 2:22 PM
Points: 14,437, Visits: 37,833
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!
Post #1454697
Posted Monday, May 20, 2013 5:12 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 40,986, Visits: 38,284
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."

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

Add to briefcase

Permissions Expand / Collapse