如何撤销对 SQL Server Analysis Services (SSAS) 的登录/连接访问 [英] How to revoke login/connection access to SQL Server Analysis Services (SSAS)

问题描述

我有一个 SQL Server Analysis Services Server 2014.我通过进入 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.

然后我在我删除的用户上下文中启动了 SQL Server Management Studio (SSMS)，使用上下文菜单中的 Run as different user 选项.

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.

令我惊讶的是，尽管该用户(无法访问 SSAS 服务器)通过 Windows 集成安全性，我仍然能够连接到 SSAS 服务器.怎么可能呢?是否还需要从其他地方删除用户才能撤销用户对 SSAS 服务器的访问权限?

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?

我还从 services.msc 控制台重新启动了 SQL Server 和 SSAS 服务，结果没有任何变化.

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

推荐答案

TL;DR; 我最初的假设是，从 SSAS 服务器实例的安全选项卡中删除用户也将撤销他连接到通过 SSMS 的 SSAS 服务器不正确.

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.

完整的故事:好的.所以这就是我最终发现的.主要的混淆是因为在数据库引擎和分析服务 (SSAS) 的情况下，SQL Server Management Studio (SSMS) 对用户登录的行为方式不同.

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).

在数据库引擎的情况下，如果您尝试使用 SSMS 登录的用户不在 Security ->Logins 节点，那么您就无法在登录屏幕之外继续前进.它给出了以下提到的错误:

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:

无法连接到..

------------------------------ 附加信息:

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

用户 'yourMachineName\jayceeka' 登录失败.(微软 SQL 服务器，错误:18456)

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

即使用户存在于 Security ->Logins 节点及其登录被禁用或连接到数据库引擎的权限在登录的属性页面中设置为 Deny 那么您也无法继续前进.您将停留在登录屏幕本身.

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 在连接到 SSAS 服务器时表现不同.是否在 SSAS 服务器属性的 Security 选项卡中添加用户并不重要(显示在我的问题帖子中)，但只要用户可以登录 SSAS 服务器所在的机器运行，然后他将能够通过 SSMS 连接 SSAS 服务器，并使用 Windows 集成安全性通过登录屏幕.

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.

SSAS 服务器属性窗口中的安全"选项卡不控制用户通过 SSMS 连接到 SSAS 服务器的能力，但它确实控制其他一切，例如连接到任何 DB、Cube 和内部其他事物的能力SSAS 服务器实例.

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.

因此，如果用户不在 SSAS 服务器属性窗口的 Security 选项卡中，那么即使他能够去，他也将无法看到 SSAS 服务器中的数据库、多维数据集等使用 Windows 集成身份验证通过 SSMS 中的登录屏幕，如下所示:

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:

在我从 SSAS 服务器中删除用户 Jayceeka 之后，她在 Databases 节点或 Assemblies 节点中看不到任何内容，而实际上是一个数据库存在于那里，如下所示:

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:

即使在 ADOMD.Net 代码中 - Open() 对 Jayceeka 用户的调用成功，但由于她没有 SSAS 服务器的权限而无法执行查询:

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");
}

这篇关于如何撤销对 SQL Server Analysis Services (SSAS) 的登录/连接访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！