Tuesday, November 19, 2013

bcp -t field terminator

Typical bcp out uses comma (csv) or tab (tsv) but there was a special request that fields should be separated by char "|". bcp.exe has a special -t option to change default field terminator (tab) to something else. So tried to use this option.

C:> bcp "select au_id, au_lname from pubs.dbo.authors" queryout "1.csv" -S. -t | -c -T
'-c' is not recognized as an internal or external command,
operable program or batch file.

The problem is | in "-t | -c " is not considered as field terminator. In command shell, | is considered as pipe command. so prior to | is one command and the result is passed to next to | command, which is "-c" here. But there is no -c command, hence the error.

So how to solve this problem? Fortunately, we can use 1 byte hex value in -t option. So since Ascii hex value for | character is 0x7C, we can put this value in -t option to avoid command pipe.

C:> bcp "select au_id, au_lname from pubs.dbo.authors" queryout "1.csv" -S. -t 0x7C -c -T

Starting copy...

23 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (23000.00 rows per sec.)

Saturday, November 16, 2013

How to force users to execute Stored Procedures only

Let's say there is a situation we want to prevent any SQL users from directly accessing any underlying tables and views. And we want SQL users to access underlying tables via stored procedures only. (I understand this is a special case and typically people don't want to do that.)

How can we implement this scenario?
If we only block one user, we first deny all DML operations against the user and then grant EXECUTE permission to the user as follows:

USE TestDB
DENY SELECT, INSERT, DELETE, UPDATE to User1
GRANT EXECUTE TO User1

If we want to apply this to any database users, we can use PUBLIC role as follows:

DENY SELECT, INSERT, DELETE, UPDATE to PUBLIC
GRANT EXECUTE TO PUBLIC