Search code examples
sql-serverssassql-server-2014

How to revoke login/connection access to SQL Server Analysis Services (SSAS)

I've got a SQL Server Analysis Services Server 2014. I revoked the access of a user by going into the security tab of properties window of the SSAS server instance and then performing remove operation.

enter image description here

Then I launched SQL Server Management Studio (SSMS) in the context of the user which I had removed by using Run as different user option from context menu.

To my surprise I'm still able to connect to the SSAS server though that user( which has no access to the SSAS server) through windows integrated security. How come it is possible? Does a user need to be removed from somewhere else as well to revoke access of a user to SSAS server?

I also restarted SQL Server as well as SSAS service from services.msc console but there was no change in the outcome.

Solution

  • TL;DR; My initial assumption that removing a user from security tab of SSAS server instance will also revoke his ability to connect to the SSAS server through SSMS was incorrect.

    The Complete story: Ok. So here is what I found out finally. The main confusion arises because how SQL Server Management Studio(SSMS) behaves differently for user logins in case of Database Engine and Analysis Services (SSAS).

    In case of database engine if the user with which you are trying to login using SSMS is not present inside Security -> Logins node then you can't move forward at all beyond the login screen. It gives below mentioned error:

    Cannot connect to ..

    ------------------------------ ADDITIONAL INFORMATION:

    Login failed for user 'yourMachineName\jayceeka'. (Microsoft SQL Server, Error: 18456)

    Even if the user is present in Security -> Logins node and its login is disabled or the permission to connect to DB engine is set as Deny in the properties pages of the login then also you can't move forward. You will remain stuck on the login screen itself.

    SSMS behaves differently while connecting to SSAS server. It doesn't matter whether a user is added in the Security tab of SSAS server properties (shown in my question post) or not, but as long as the user can login on the machine where SSAS server is running then he will be able to connect the SSAS server through SSMS and go past the login screen using Windows Integrated Security.

    The Security tab in the properties window of SSAS server doesn't control a user's ability to be able to connect to SSAS server through SSMS but it does control everything else like ability to connect to any DB, Cube and other things inside the SSAS server instance.

    So in case the user is not present in Security tab of SSAS server properties window then he will not be able to see the databases, Cubes etc in the SSAS server even if he is able to go past the login screen in SSMS using Windows Integrated authentication as shown below:

    enter image description here

    After I removed user Jayceeka from the SSAS server then she can't see anything inside the Databases node or Assemblies node when actually one database is present there as shown below:

    enter image description here

    Even in ADOMD.Net code - The Open() calls succeeds for Jayceeka user but fails to execute the query as she doesn't have rights on the SSAS server:

    DataSet ds = new DataSet();
AdomdConnection myconnect = new AdomdConnection(@"provider=olap;datasource=.;Catalog=myDbInSsasServer"); 
AdomdDataAdapter mycommand = new AdomdDataAdapter();
var adomdCommand = new AdomdCommand();
adomdCommand.CommandText = "SELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAME FROM $system.MDSchema_Cubes WHERE CUBE_SOURCE = 1";
mycommand.SelectCommand = adomdCommand;
mycommand.SelectCommand.Connection = myconnect;

try
{
  //Open call succeeds. This is akin to be able to connect in SSMS and go past the login screen using Windows Integrated authentication
  myconnect.Open();
  //this call fails. This is akin to the fact that she can't see databases so of course can't query it.
  adomdCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
   MessageBox.Show("error in executing query on the SSAS Server");
}