SQL Server CSV From Data

A powerful way to convert row-level data to CSV

The ability to convert row-level data into CSV values can be exceedingly useful.

Using the STUFF function in combination with the FOR XML PATH('') command is a powerful way of achieving this.

DECLARE @OUTPUT VARCHAR(1000)
SELECT @OUTPUT = STUFF(
  (SELECT ',' + MyStringVal
   FROM MyTable
   WHERE MyIntVal = SomeIntVal
   FOR XML PATH('')
  ), 1, 1, '')

The STUFF function "inserts a string into another string", and in this case is purely used to remove the leading comma.

The FOR XML PATH('') is the key, and takes row-level data and turns it into a string of XML data. By using PATH('') we're telling SQL to not wrap the XML with a root node.

Finally, the use of ',' + MyStringVal results in the column having no name... which means SQL will not wrap the individual values in an XML node either.

SQL Server Dynamic Script Permissions

Dealing with permissions when modifying data using sp_executesql

Thanks to the impending GDPR we have been implementing a series of changes to our systems, which use Microsoft SQL Server (2008+)

To aid in the speed of development, I have written a bunch of SQL scripts which require the use of sp_executesql commands.  What I had forgotten, however, is the small matter of permissions.

When you run a stored procedure (sproc), the permissions used are those of the user who created the sproc.  Which is great and means that you can run that sproc via a restricted access login.

The problem is that if the sproc uses sp_executesql to run dynamic script, the permissions switch to the restricted access login... the result being that you cannot SELECT, INSERT, UPDATE, DELETE.

The solution we're using involves creating a local WITHOUT LOGIN database user...

CREATE USER dynamicdatauser WITHOUT LOGIN
EXEC sp_addrolemember 'db_datareader', 'dynamicdatauser'
EXEC sp_addrolemember 'db_datawriter', 'dynamicdatauser'

And then allocating that user directly to the sproc...

CREATE PROCEDURE dbo.[MySproc]
  @PARAMS INT
WITH EXECUTE AS 'dynamicdatauser'
AS

To the best of my knowledge, giving the user db_datareader and db_datawriter permissions should not have any security implications as the WITHOUT LOGIN should block any ability for the account to be used outside of the code environment.