需要视图帮助 [英] Need Help with Views
问题描述
您好
我需要这方面的帮助。你能帮忙吗。
I need help with this situation. Can You please help.
SQL Server 2014企业版64位
SQL Server 2014 enterprise edition 64 bit
Windows服务器2012 R2标准版64位
数据库A 表A
$
创建表格表A
（
[ColA] Varchar（10）NOT NULL，
[ColB] [datetime] NOT NULL，
[ColC] [datetime] NOT NULL，
[ColD] [datetime] NOT NULL，
[ColE] [datetime] NOT NULL，
[ColF] [datetime] NOT NULL
）ON [PRIMARY]
数据库B ViewA
有没有办法根据数据库A中的表A在数据库B中创建ViewA。
我不想为此使用跨数据库所有权链接。
数据库B中的UserA应该正在运行仅限数据库B中的视图。数据库B中的userA不应该访问数据库A中的表。
查询;
使用DatabaseB
GO
从dbo.vwTableA中选择*
错误消息：
Msg 916，Level 14，State 1，Line 4
服务器主体UserA无法使用访问数据库"DatabaseA"在良好的安全背景下
Windows server 2012 R2 Standard Edition 64 Bit
Database A TableA
Create Table TableA
(
[ColA] Varchar(10) NOT NULL,
[ColB] [datetime] NOT NULL,
[ColC] [datetime] NOT NULL,
[ColD] [datetime] NOT NULL,
[ColE] [datetime] NOT NULL,
[ColF] [datetime] NOT NULL
) ON [PRIMARY]
Database B ViewA
Is there a way to create ViewA in Database B based on Table A in Database A.
I dont want to use Cross-Database Ownership Chaining for this.
The UserA in Database B should be running the views in database B only. The userA in Database B should not have access to tables in Database A
Query;
Use DatabaseB
GO
Select * from dbo.vwTableA
Error Message:
Msg 916, Level 14, State 1, Line 4
The server principal UserA is not able to access the database "DatabaseA" under the currrent security context
推荐答案
只要您希望它成为一个视图，你的前景黯淡。 UserA需要是DatabaseA中的用户，如果您不想启用跨数据库所有权链接，userA还需要对基础表的SELECT权限。
As long as you want it to be a view, your prospects are bleak. UserA needs to be a user in DatabaseA, and if you don't want to enable cross-DB ownership chaining, userA also needs SELECT permission on the underlying table.
如果您可以接受它要成为存储过程或多语句功能，您可以 使用证书签名。您在数据库A中创建证书，并从证书创建用户并授予该用户对该表的权限。然后 你
将证书复制到数据库B，其中 你用它来签署函数/存储过程。
If you can accept it to be a stored procedure or a multi-statement function, you can use certificate signing. You create a certificate in database A, and create a user from the certificate and grant that user permission on the table. Then you copy the certificate to database B where you sign the function/stored procedure with it.
我在文章中详细介绍了这种技术 存储过程中的包装权限 。
I have written about this technique in detail in my article Packaging Permissions in Stored Procedures which you find on my web site.
这篇关于需要视图帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
