如何对具有访问其他模式的对象的模式设置权限?

USE DATABASE SANDBOX;

--Create Logins
CREATE LOGIN UserOne WITH Password='Hello123';
CREATE LOGIN UserTwo WITH Password='Hello123';

--Create Database Users
CREATE USER UserOne;
CREATE USER UserTwo;

--Create the Test Schemas
CREATE SCHEMA SchemaOne AUTHORIZATION UserOne;
CREATE SCHEMA SchemaTwo AUTHORIZATION UserTwo;

--Create a View on SchemaOne
CREATE VIEW SchemaOne.ViewOne
AS SELECT 1 as TestColumn;

--Create a View on SchemaTwo
CREATE VIEW SchemaTwo.ViewTwo
AS SELECT * FROM SchemaOne.ViewOne;

--Test that the SchemaOne
EXEC('select * from SchemaOne.ViewOne') AS USER = 'UserOne'
--1

EXEC('select * from SchemaTwo.ViewTwo') AS USER = 'UserOne'
--The SELECT permission was denied on the object 'ViewTwo', database 'SANDBOX', schema 'SchemaTwo'.

--Create a stored procedure to safely expose the view within SchemaTwo to UserOne who's default Schema is
--SchemaOne.
CREATE PROCEDURE SchemaTwo.proc_SelectViewTwo
AS
    select * from SchemaTwo.ViewTwo;

--Grant execute rights on the procedure
GRANT EXECUTE ON SchemaTwo.proc_SelectViewTwo TO UserOne;

--Test the 
EXECUTE AS LOGIN='UserOne';
    Exec SchemaTwo.proc_SelectViewTwo;
revert;

我在评论中建议的另一种方法是使用数据库角色来控制对多个模式的访问。使用上面解决方案中定义的主体,您可以这样使用数据库角色:

EXEC sp_addrole 'CrossSchemaRole';
EXEC sp_addrolemember 'CrossSchemaRole','UserOne';

GRANT SELECT ON SCHEMA::SchemaOne TO CrossSchemaRole;
GRANT SELECT ON SCHEMA::SchemaTwo TO CrossSchemaRole;

EXECUTE AS LOGIN='UserOne';
    select * from SchemaTwo.ViewTwo;
revert;

• Securing SQL Server
• SQL Server Permission Hierarchy
• Security Overview for Administrators

当然,您授予的权限是用户而不是对象,仅此而已。如果您或其他模式的所有者希望允许其他用户访问其中的对象(无论是表、视图还是其他),那么这取决于其他模式的拥有者。仅仅因为你作为开发人员可以编写一个访问其他模式中对象的过程,并不意味着运行你的过程的任何人都应该被允许这样做。角色是要走的路。