Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It seems to me, that you are not waiting for an SQL code in the answer on your question. The main aspect of you question is the security aspect. What should you do to implement your requirement without <em>sysadmin</em> privileges and without a new security hole? This is your real question I think.</p> <p>I see at least 3 ways to solve your problem. But first of all a short explanation why sysadmin privileges exists in all solutions based on <em>Extended Stored Procedures</em>. Extended Stored Procedures like <code>xp_cmdshell</code> are very old. They existed at least before SQL Server 4.2, the first Microsoft SQL Server running under the first Windows NT (NT 3.1). In the old version of SQL Server I was not security restriction to execute such procedures, but later one made such restrictions. It is important to understand, that all <strong>general purpose</strong> procedures which allow starting <strong>any process</strong> under SQL Server account like <code>xp_cmdshell</code> and <code>sp_OACreate</code> <strong>must</strong> have <em>sysadmin</em> privileges restriction. Only a <strong>task oriented</strong> procedures with a clear area of usage and role based permissions can solve the problem without a security hole. So this is the 3 solution ways which I promised before:</p> <ul> <li>You create a new SQL account on you SQL server with <em>sysadmin</em> privileges. Then you create a stored procedure which use some from <em>Extended Stored Procedures</em> like <code>xp_cmdshell</code> or <code>sp_OACreate</code> and technically implement you requirements (export some information into a CSV file). With respect of <strong>EXECUTE AS</strong> Clause (see <a href="http://msdn.microsoft.com/en-us/library/ms188354.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms188354.aspx</a>) you configure the created stored procedure so, that it runs under the account with <em>sysadmin</em> privileges. You delegate the execution of this procedure to users with a some SQL role, to be more flexible from the side of delegation of permission.</li> <li>You can use <em>CLR Stored Procedures</em> instead of <code>xp_cmdshell</code> and <code>sp_OACreate</code>. You should also use role based permissions on the procedure created.</li> <li>The end-user doesn’t call directly any SQL stored procedure what you create. There is exists a piece of software (like WCF service or a Web Site) which call your SQL stored procedure. You can implement the export to CSV file inside of this software and not inside of any SQL stored procedure.</li> </ul> <p>In all implementation ways you should exactly define <strong>where you will hold the password</strong> of the account with which you access to the file system. There are different options which you have, all with corresponding advantages and disadvantages. It's possible to use impersonation to allow access to the file system with the end-user‘s account. The best way depends on the situation which you have in your environment.</p>
    singulars
    1. This table or related slice is empty.
    1. POExporting query results to a file on the fly
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USOleg
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POExporting query results to a file on the fly
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTBountyClose
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload