If you have ever used Microsoft SQL Server (or MSSQL) and had the need to programmatically write data from a SELECT statement out to a file, you may have found that your options to do so are cumbersome and involve leaping through significant security-imposed hurdles that can make a developer nervous, as the last thing any of us would want to do is “bend the rules too far” and allow for security restrictions to become so lax that external intrusions become an increased threat.
Fortunately, Microsoft provides command line tools that allow for their output to be captured or written directly to files, all without making any security policy changes in SQL Server. The only “cost” of this feature is an SQL Server account with minimal read-only privileges for the database against which the SQL code is being run.
Now granted, there is always some hotshot who believes that changing the security settings in SQL Server to allow for the Special Stored Procedures that enable saving to files to run might be a good idea, and that would be the right way to perform such tasks, but that person won’t be laughing when an unintended side effect of those changes invites a remote compromise. And, realistically, we as database developers should work within the most restrictive security permission settings as possible, with the thought of altering those settings only being a last resort.
Read: Learn the Basics of Extracting Data in SQL in Five Minutes
Where Do I Find SQLCMD.EXE?
As most people who interact with SQL Server typically use the SQL Server Management Studio (SSMS), they may not be readily familiar with, or aware of the existence of, a command line utility. The command line utility in question is called SQLCMD.EXE and it is bundled with SQL Server when it is installed and it is usually available in your Windows System Path. However, it is best to confirm that this file does indeed exist on your system before proceeding. This can be done using the where command in the Windows Command Prompt:
Using the where command to confirm the location of SQLCMD.EXE
Even if SQLCMD.EXE is already in your system’s path, knowing the full path to the file can be useful if you wish to automate the execution of SQL code with its output being saved to a file. Say, for example, you need to create a list of authorized users every week and then upload that to a remote server that checks against that list. The scripting technology that you might use to execute that process may not have access to the same path, especially if it is run under a different Windows account than your own (such as the Network Service system account), so specifying the full path to point to SQLCMD.EXE would be the easiest way to make that script work.
Using SQL to Convert Data to CSV
SQLCMD.EXE will parse and execute any valid SQL code. It can even be used to run SQL code which writes data into, performs administrative tasks on, or does maintenance tasks for, a database if an SQL Server account with the correct privileges and credentials is provided. However, if the goal is to write data to a CSV file, then the output of the SELECT statement, which creates the output set, must itself be data which is easily converted into CSV data. SQLCMD.EXE can be configured to provide the comma delimiter for such files. Consider the following demonstration data set:
Demonstration Data Set
This, of course, is a very trivial SELECT statement. However, any T-SQL batch which outputs any record set could be outputted here. This could be the result of a very complex and tedious SQL batch and it would still apply. Of course, properly formatted CSV records have quotation marks around each value, and any quotation marks within the value are escaped as doubled quotation marks. The T-SQL command QUOTENAME solves this problem:
SQL Quotename
QUOTENAME escaped the quotation marks in the first entry and placed quotation marks around all the other text values. QUOTENAME has a maximum string length limit of 128 characters. Anything longer than this will need to be split up or truncated. Examples here are deliberately smaller than 128 characters.
Applying QUOTENAME to the first SELECT statement gets us the following:
SQL Quotename applied to entire table
Here is the SQL code:
# Very important, or else the rowcount will appear in SQLCMD.exe output.
set nocount on;
select
quotename(ltrim(rtrim(public_employee_id)), '"'),
quotename(ltrim(rtrim(last_name)), '"'),
quotename(ltrim(rtrim(middle_name)), '"'),
quotename(ltrim(rtrim(first_name)), '"'),
quotename(ltrim(rtrim(phone_number)), '"'),
quotename(ltrim(rtrim(email)), '"')
from
CSV_Export_Demo.dbo.employees;
If you do not want to bother applying the quotename function to every column name, it is possible to query the information_schema.columns system view for a given table and dynamically build it, but for the purposes of this article it is not necessary, and practically speaking, unless you modify the export logic on a regular basis, that approach is overkill. At this point, make sure the SQL code is saved somewhere that is easy to access via the Windows Command Prompt.
Read: How to Find the Worst Performing T-SQL Code
How to Create a Read-Only SQL Login and User
Before we can write out any data, we need to be able to read it first. Under no circumstances should you use any SQL administrator account, such as sa, or your own Windows account should it have write access or administrator access at the command line. The main reason for this is not necessarily a security breach, but say the code file you load gets changed inadvertently to something destructive. There would be nothing stopping the execution of such code as an administrative user or as a user who has write access.
Treat any file you pass into SQLCMD.EXE like any other untrusted input in a traditional program, or like any SQL code you would build in any other program. Do not trust it by default and execute it using the least user privilege necessary.
SQL Server has two kinds of user access:
- Logins – which grant access to the SQL Server Instance itself.
- Users – which grant access to a particular database within a given SQL Server Instance.
In order for SQLCMD.EXE to access this data as a read-only user, a new login will need to be created if it does not exist already, and a new user will need to be mapped to that login and granted read-only access to the database. The T-SQL code below creates a user called exporter_readonly for this purpose:
use master;
–- Replace with a suitable password.
create login exporter_readonly with password='P@$$w0rd123';
-- Prevents the expiration of the password and prevents need to change password after first login.
-- This may not be the best option for your security preferences.
alter login exporter_readonly with check_policy=OFF;
-- Replace the database below with your database.
use CSV_Export_Demo;
create user exporter_readonly for login exporter_readonly;
exec sp_addrolemember 'db_datareader', 'exporter_readonly';
T-SQL Code to create a new login and read-only user
Running this code combines all of these and will give the following output:
Read: Performing SQL Transactions with Visual Basic
Verifying User Access in SQL
Optionally, you may want to verify that the access for the exporter_readonly user is indeed properly set. To verify the Login, expand the highest-level security heading (the one under Databases) and expand the Logins heading below that. Right-click on the exporter_readonly user and click the Properties option. If you do not see exporter_readonly user, you may need to right-click on the Logins heading and click the Refresh option:
Finding the new login
Upon clicking the Properties option in the context menu, the following dialog appears. Ensure that the login name is exporter_readonly and that none of the checkboxes for Enforce password policy, Enforce password expiration or User must change password at next login are checked.
Note: These relaxed security settings are used because this is a tutorial demonstration. Once everything is working as expected, then these options can be set, but be prepared to follow through on making any password updates to any command line calls to SQLCMD.EXE with any new passwords.
New Login Properties
If everything is as it should be, click the Cancel button to make sure any changes aren’t inadvertently saved.
Conclusion to Part One of Command Line SQL
So far, we have learned how to find SQLCMD.EXE, convert data to a CSV file format, and create a read-only use for MSSQL Server. In the second part of this article, we will use SQLCMD.EXE to write and save data to an SQL Server database.
Read more database programming and database administration tutorials.
