Hey Eric -
SQL Server doesn't allow you to map a standard SQL login to to an existing AD user in a SQL database via ALTER USER, so you're really fighting the way SQL Server is designed to work:
(from SQL Server BOL, ALTER USER topic) :
The WITH LOGIN clause enables the remapping of a user to a different login. Users without a login, users mapped to a certificate, or users mapped to an asymmetric key cannot be re-mapped with this clause. Only SQL users and Windows users (or groups) can be remapped. The WITH LOGIN clause cannot be used to change the type of user, such as changing a Windows account to a SQL Server login.
Since SQL Server explicitly disallows what you want to do, I'd think you're going to need to go back to the drawing board and do one of the following:
- Get new standard SQL Server logins/users added to the database and clone the existing permission sets to them
- Get Tableau into AD
- Create some sort of staging database with cuts of the original data, and then re-secure that data using a Tableau User filters.
WITH LOGIN is not the clause I would need to use. Here is a post that explains exactly what I'm trying to accomplish:
And here's a sample query (the first 3 lines would need to precede the SELECT statement):
DECLARE @uid uniqueidentifier
SET @uid = convert(uniqueidentifier, 'user_guid_goes_here')
SET CONTEXT_INFO @uid
SELECT TOP 1000 *
You're not going to be able to change context information via Tableau using the TSQL before the SELECT. We only fire SELECT statements, really (that's a bit of an overstatement, but whatever).
Perhaps you can encapsulate all this logic in a SQL Table Valued Function or in a pinch, a stored procedure? Then you might be able to get this to work.
I'm not an SQL expert, so it would help if you could clarify a little bit...
Are you saying that I can have Tableau run a stored procedure, and that stored procedure can include these SQL clauses and SELECT the view?
What we're talking about here is fairly advanced - so you're going to need your SQL Server Administrator to help you.
In SQL Server, Stored Procedures and Table Valued Functions are ways that you can encapsulate multiple commands.
For example, one might write a Stored Procedure that does something trivial, like execute a single SELECT statement. Or, that Stored Procedure might be hundreds of lines long, with very complex control-of-flow statements that run different SELECT statements based on the output of other commands.
Since Tableau doesn't allow for firing "initialization SQL statements" (those first 3 of 4 lines of code), you could get someone to put all four lines of SQL into a SQL Stored Procedure.
Then, YOU could call the stored procedure from Tableau using a "sneaky" technique. You'd use Tableau's ability to fire custom SQL to fire a statement which calls the stored procedure:
OPENQUERY([LINKSERVER],'SET FMTONLY ON; exec database.user.my_stored_proc ''user_guid_goes_here')
Why is this so complex? Because you're trying to make Tableau do something it's not designed to do
Edit: Note -- I've never tried to do exactly what you're doing myself. But it should be possible.
Message was edited by: Russell Christopher