Tableau 9.3 brings a wonderful new feature called Initial SQL with Parameters which allows you to declare a specific set of SQL commands to be run before any queries within a particular Tableau Server user’s session. One of the initial use cases is Oracle VPD. It can also be used in Microsoft SQL Server with a single technique to solve two issues:

1. Passing the Tableau Username to a Stored Procedure (previously only possible using the JavaScript API )
2. How to use the WHERE Method of row level security (see the Row Level Security post for full explanation), when the usernames you need for data security are not users in the database. If the usernames are users in the database on SQL Server, you can use Impersonate User in SQL Server as per the section in this post titled Recommendation: Use the JOIN Method unless the WHERE Method is “easy”. That is to say, this technique is only necessary when the usernames and entitlements exist only in the database tables, and not as part of the database security model.

We still need some setup in our database, specifically a table that lets us match SQL Server connection sessions to a particular username. Each SQL Server session has something called an SPID, which can be accessed through the variable @@SPID . You’ll want to create a table for your Tableau based SPID sessions; something like [Tableau Sessions]. You only need two columns: [Username] nvarchar(n) and [Current SPID] int

Your initial SQL should be the following:

DECLARE @query as nvarchar(300) = 'SELECT [Current SPID] FROM [Tableau Sessions] WHERE [Current SPID] = @@SPID'
EXECUTE sp_executesql @query
IF @@rowcount = 0
BEGIN
INSERT INTO [Tableau Sessions] VALUES ([TableauServerUser], @@SPID)
END
ELSE
BEGIN
UPDATE [Tableau Sessions]
SET username = [TableauServerUser]
WHERE [Current SPID] = @@SPID
END

 

 

[TableauServerUser] is a variable that Tableau automatically substitutes with the logged in server’s username. The other possible tokens are listed in the blog post I linked to at the beginning.

Basically, you use this table to give you a mapping of SPID -> Username.

Then in any SP or View you create, you can use this as a lookup to drive your security calculations in a WHERE clause:

WHERE (dbo.table.[Security_Key] IN
(SELECT        username
FROM            dbo.[Tableau Sessions]
WHERE        ([Current SPID] = @@SPID))
)

or more likely, you’ll use the [Tableau Sessions] table to filter your [Security Table], which then returns the value that will filter the data table:

WHERE dbo.table.[Security_Key] IN (
SELECT [s].[Security_Key]
FROM [Security] [s]
WHERE [s].[Username] = (
SELECT [Username] FROM [USERNAME_SPID]
WHERE [SPID] = @@SPID
)
)