Sql-server – comma delimited column fix

The SQL Server Analysis Services drivers aren't actually installed as a part of the normal SQL Server installation unless you install SSAS on the server.

You can download the MSOLAP driver from the SQL Server feature pack and install it. Then you can create the linked server. Don't forget to install the 64bit install if you have a 64bit SQL Server or the 32bit if you have a 32bit SQL Server.

A user that was explicitly created with WITHOUT LOGIN cannot be mapped to a login after the fact.

A user that was created with a login and then lost its association, e.g. because the database was moved to another server, can be remapped by first creating a new login (any name) and then using the ALTER USER ... WITH LOGIN = ... command.

To demonstrate I am going to create a database tst and then create a login tstlogin and an associated user tstuser within tst:

CREATE DATABASE tst;
GO
CREATE LOGIN tstlogin WITH PASSWORD = 'passw8rd';
GO
USE tst;
GO
CREATE USER tstuser FOR LOGIN tstlogin;

To show correct association we can use the following query:

SELECT DP.sid,DP.name user_name, SP.name login_name
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP
ON DP.sid = SP.sid
WHERE DP.name = 'tstuser';

This results in

SID                                        user_name      login_name
------------------------------------------ -------------- --------------
0xBF3D0CC874C72B469BEB4AA93845F59E         tstuser        tstlogin

Now we will drop the login tstlogin

DROP LOGIN tstlogin;

and recheck our user:

SELECT DP.sid,DP.name user_name, SP.name login_name
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP
ON DP.sid = SP.sid
WHERE DP.name = 'tstuser';

which does not show an associated login anymore:

SID                                        user_name      login_name
------------------------------------------ -------------- --------------
0xBF3D0CC874C72B469BEB4AA93845F59E         tstuser        NULL

Next step is to create a new login. (I chose a diffenert name for demonstration purposes, but it could be the same name too.)

CREATE LOGIN tstlogin2 WITH PASSWORD = 'passw8rd';

To associate that new login with our user we can use the ALTER USER statement:

ALTER USER tstuser WITH LOGIN = tstlogin2;

Checking the association once again:

SELECT DP.sid,DP.name user_name, SP.name login_name
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP
ON DP.sid = SP.sid
WHERE DP.name = 'tstuser';

shows that the ALTER statement was successful:

SID                                        user_name      login_name
------------------------------------------ -------------- --------------
0xBF3D0CC874C72B469BEB4AA93845F59E         tstuser        tstlogin2

If you actually need to change a "WITHOUT LOGIN" user to be a "with login" user, you will have to drop that user and recreate it. Make sure in that case to script out the permissions first, as dropping a user causes SQL Server to forget those.