June 25, 2012 at 11:37 am
/*This query works fine to import the data to .csv files, if we have admin access, as there is "master.dbo.xp_cmdshell ".
I am trying to query this in such a way where normal user who do not have permissions to run xp_cmdshell should execute.
Tried different ways of taking out xp_cmdshell but apparently it fails. Can someone help me to run this query with normal credentials.
*/
DECLARE @var nvarchar(MAX)
DECLARE curRunning
CURSOR LOCAL FAST_FORWARD FOR
select name from sysobjects where type = 'U'
Open curRunning
Fetch NEXT From curRunning into @var
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Exec nvarchar(MAX)
--iSSUE WITH FOLLOWING LINE
set @Exec = 'exec master.dbo.xp_cmdshell ''bcp DB.dbo.' + @var + ' out E:\SOME PATH\' + @var + '.csv -t "|" -c -T '''
execute sp_executesql @Exec
FETCH NEXT FROM curRunning into @var
END
close curRunning
DEALLOCATE curRunning
June 25, 2012 at 12:33 pm
well, BULK INSERT and BCP -IN both have some big assumptions: the file is on/accessible from the server itself, and of course permissions on both the destination table and the path to the file.
is this process always going into one table, or does the user get to pick the target table to import into as well a s the file?
if it was always the same target table, and you are sure end users really need that access, i would create a procedure with EXECUTE AS, where the end user only passes in the file name.
then i'd use BULK insert instead of bcp anyway.
--the EXECUTE AS must be a user in the database...not a login
CREATE procedure pr_CallBoostedSecurityProcess(@PathToFile varchar(1024))
WITH EXECUTE AS 'superman'
AS
BEGIN
--because BULK INSERT does not allow @parameters, we switch to dynamic SQL:
declare @sql varchar(8000)
--bulk insert won't take a variable name, so make a sql and execute it instead:
set @sql = 'BULK INSERT BULKACT FROM ''' + @PathToFile + ''' '
+ ' WITH (
DATAFILETYPE = ''char'',
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n'',
FIRSTROW = 1
) '
print @sql
exec (@sql)
END
create user [ClarkKent] without login
GRANT EXECUTE ON pr_CallBoostedSecurityProcess TO [ClarkKent]
Execute As USER= 'ClarkKent'
EXEC pr_CallBoostedSecurityProcess 'E:\SOME PATH\somefile.txt'
Lowell
June 25, 2012 at 12:41 pm
Lowell (6/25/2012)
well, BULK INSERT and BCP -IN both have some big assumptions: the file is on/accessible from the server itself, and of course permissions on both the destination table and the path to the file.is this process always going into one table, or does the user get to pick the target table to import into as well a s the file?
if it was always the same target table, and you are sure end users really need that access, i would create a procedure with EXECUTE AS, where the end user only passes in the file name.
then i'd use BULK insert instead of bcp anyway.
--the EXECUTE AS must be a user in the database...not a login
CREATE procedure pr_CallBoostedSecurityProcess(@PathToFile varchar(1024))
WITH EXECUTE AS 'superman'
AS
BEGIN
--because BULK INSERT does not allow @parameters, we switch to dynamic SQL:
declare @sql varchar(8000)
--bulk insert won't take a variable name, so make a sql and execute it instead:
set @sql = 'BULK INSERT BULKACT FROM ''' + @PathToFile + ''' '
+ ' WITH (
DATAFILETYPE = ''char'',
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n'',
FIRSTROW = 1
) '
print @sql
exec (@sql)
END
create user [ClarkKent] without login
GRANT EXECUTE ON pr_CallBoostedSecurityProcess TO [ClarkKent]
Execute As USER= 'ClarkKent'
EXEC pr_CallBoostedSecurityProcess 'E:\SOME PATH\somefile.txt'
Did I miss something in the OPs post? It looks to me like he(she) is exporting data, not importing data.
June 25, 2012 at 12:48 pm
where the heck did i put my jump to conclusions mat?
mea culpa, i would have sworn i read import via BCP/xp_cmdshell. it was the "This query works fine to import the data to .csv files" part that kept me from reading the details more.
since we are looking for a different way to export data, i'll throw my CLR project out there then:
http://sqlclrexport.codeplex.com/
with that project, you can give them access to a stored proc to export data out in a variety of formats, including csv:
EXECUTE CLR_ExportTableToCSV @TableName = 'Products',
@FilePath = 'C:\Data',
@FileName = 'Products_export.csv',
@IncludeHeaders = 1
--a Query instead?
EXECUTE CLR_ExportQueryToCSV @QueryCommand = 'SELECT * FROM Products WHERE CodeItem = 42',
@FilePath = 'C:\Data',
@FileName = '\Products_export.csv',
@IncludeHeaders = 1
Lowell
June 25, 2012 at 12:50 pm
This query makes us to load the data from .csv files to database. But, my question is to how to load the data from database to .csv files without using xp_cmdshell permissions
June 25, 2012 at 1:18 pm
lowell,
how do you set the delimiter '|' option
June 25, 2012 at 1:28 pm
there's a different proc for that:
in this example, you'd change '[~~]' to '|':
take a look at the documentation, i think i fleshed it out with a lot of examples;
http://sqlclrexport.codeplex.com/documentation
EXECUTE CLR_ExportTableToCustomDelim @TableName = '@TableVariable',
@FilePath = 'C:\Data\',
@FileName = '\Results_export.txt',
@IncludeHeaders = 1,
@CustomDelimiter = '[~~]'
Lowell
June 25, 2012 at 1:37 pm
Thanks lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply