在实时和日志传送的实例之间分离权限 [英] Separating permissions between live and log shipped instances

问题描述

大家好，

我需要为我支持的环境中的一组用户设计权限。 

I've got a requirement to engineer permissions for a group of users in an environment I support. 

实例A托管多个数据库，其中一个是日志传送到报告服务器，实例B. 出于性能原因，我们正在尝试强制用户针对报告（日志已发送）实例B运行报告。 

Instance A hosts multiple DBs, one of which is log shipped to a reporting server, Instance B.  We're trying to force users to run reports against the reporting (log shipped) Instance B for performance reasons. 

根据我的理解，我无法在实例A的数据库级别删除他们的读访问权限，因为这样做会在更改时删除他们对报表实例B的访问权限是日志传送过来。  

I can't remove their read access at the DB level on Instance A as I understand it, as doing so would remove their access to the Reporting Instance B when the change is log shipped over.  

我无法删除他们在实例A级别的读取权限，因为用户还需要访问该实例托管的其他数据库。

I can't remove their read access at Instance A level as the users also need access to other DBs hosted by that instance.

具有一些SQL经验的客户应用程序管理员之一已经提到使用SQL角色来限制对实例A上的特定表的访问。 这可能吗？ 有人能指出我描述这个的文章吗？ 到目前为止，我的Bing-Fu
和Google-Fu已经惨遭失败。

One of the customer's application managers with some SQL experience has mentioned using SQL roles to restrict access to the specific tables on Instance A only.  Is this possible?  Can someone point me at an article describing this?  My Bing-Fu and Google-Fu have failed me miserably so far.

提前感谢任何指针。

Jason

推荐答案

不，在一般情况下，这是不可能的，因为对于日志传送，日志传送数据库是一个完美的复制品源数据库。因此，两个实例上的所有角色和权限都是相同的。你唯一可以玩的是服务器级别的权限
，但这对你来说似乎也是遥不可及。

No, in the general case, that is not possible, since with log shipping the log-shipping database is a perfect replica of the source database. So all roles and permissions will be the same on both instances. The only thing you can play with is the permissions on server level, but this seems also to be out of reach for you.

然而，可能有幸运的情况允许解决方案。

There may however be lucky circumstancess that permits for a solution.

一种情况是，所有报告都基于仅用于这些报告的存储过程。在这种情况下，这些过程可以检查主机名，可能与检查标识这些用户的角色相结合。

One is if all reports are based on stored procedures that are only used for these reports. In this case, these procedures could make a check on the hostname, possibly in combination with check for a role that identifies these users.

登录触发器也可以是一个选项。这假定报表工具直接连接到此数据库，并且用户无法首先连接到另一个数据库，然后在报表工具中更改为此数据库。在这种情况下，登录触发器可以
检查数据库和用户的组合并在门口停止它们。

我假设我们正在谈论的用户是Windows登录。它们是SQL登录，还有一个解决方案 - 确保两个实例上的SID不同。

A logon trigger can also be an option. This assumes that the report tool connects directly to this database and that users cannot first connect to another database and then change to this database in the report tool. In this case, the logon trigger could check for the combination of database and user and stop them at the gate.
I'm assuming the users we are talking about are Windows logins. Would they be SQL logins, there is also a solution - make sure that SIDs are different on the two instances.

如果这些都不适合你，也许你应该考虑事务复制。设置和监视更复杂，但在应用新日志时不必踢出用户也没有问题。您可以在本地配置用户和权限。

If none of these fits you, maybe you should consider transactional replication instead. It is more complex to set up and monitor, but there is no issues with having to kick out users as you apply new logs. And you can configure users and permissions locally.

这篇关于在实时和日志传送的实例之间分离权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！