Permissions problem running bcp

• Sigerson

  Hall of Fame

  Points: 3697

  More actions

  May 20, 2013 at 12:46 pm

  #394810

  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 @sqlvarchar(255)

  DECLARE @FileNamevarchar(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

  SSC Guru

  Points: 323518

  More actions

  May 20, 2013 at 1:07 pm

  #1616753

  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

  SSC Guru

  Points: 1004406

  More actions

  May 20, 2013 at 5:12 pm

  #1616813

  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...

  [font="Courier New"]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'.[/font]

  --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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)