Exporting data using T-SQL... something opposite of BULK INSERT.

Hello-
We have to export data as part of processing and save it as a flat file.
What developers did was to use xp_cmdshell and export data that way.
It solved that problem but now the account calling it has be a member of
System Administration Server Role... a security risk!
Is there any other way of exporting data... with limited rights?
Regards,
MZeeshan
Do it using an app external to SQL Server? Then all they need to be is
db_datareader and be able to execute the stored procedure that generates the
results.
Or, lock down your SQL Server. Just because the job runs as SA doesn't mean
that anybody can do anything with it... they have to get to it first.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com...
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshan
|||MZeeshan wrote:
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
Could you not set up a Text File data source and add it as a linked
server, then INSERT INTO ( SELECT FROM ) it?
|||Hi MZeeshan,
You might also want to setup a SQL Server Agent proxy account allows SQL
Server users who do not belong to the sysadmin fixed server role to execute
xp_cmdshell. The administrators can assign appropriate security permissions
to the proxy account. When xp_cmdshell is invoked by a user who is a member
of the sysadmin fixed server role, xp_cmdshell will be executed under the
security context in which the SQL Server service is running. When the user
is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL
Server Agent proxy account, which is specified using
xp_sqlagent_proxy_account. If the proxy account is not available,
xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0 and
Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is
always executed under the security context of the Windows 9.x user who
started SQL Server.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exporting data using T-SQL... something opposite of BULK
INSERT.
>thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==
>X-WBNR-Posting-Host: 208.250.29.8
>From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>Subject: Exporting data using T-SQL... something opposite of BULK INSERT.
>Date: Thu, 24 Mar 2005 11:57:03 -0800
>Lines: 13
>Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hello-
>We have to export data as part of processing and save it as a flat file.
>What developers did was to use xp_cmdshell and export data that way.
>It solved that problem but now the account calling it has be a member of
>System Administration Server Role... a security risk!
>Is there any other way of exporting data... with limited rights?
>--
>Regards,
>MZeeshan
>
|||Have you considered using DTS ? Unless there are some really really complex
requirement in the export DTS will probably handle anything you need. You
can define a DTS job and allow a user account to run it and avoid the
security problem you described.
"MZeeshan" wrote:

> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshan