授予视图的选择权限,但不授予基础对象的选择权限

拥有视图的同一用户是否也拥有底层表?否则,表的所有者需要使用grant选项授予视图所有者权限。如果同一个用户同时拥有表和视图,那么对视图授予权限就足够了。

ALTER AUTHORIZATION ON reporting.MyViewName TO dbo

在上面的例子中 dbo 拥有表的用户是 reporting.MyViewName 正在访问

this forum 有帮助。

CREATE USER [Reports] FOR LOGIN [Reports] WITH DEFAULT_SCHEMA = Reports
CREATE SCHEMA Reports AUTHORIZATION Reports --Auth as Reports was the key piece of information that I had missed.
GO
CREATE ROLE Reporting AUTHORIZATION db_securityadmin
GO
exec sp_addrolemember @rolename = 'Reporting', @membername = 'Reports'
GO
GRANT CREATE VIEW TO Reporting
GRANT CREATE TABLE TO Reporting

GRANT SELECT, VIEW DEFINITION ON [dbo].[zName] TO Reporting;

仅供参考-对于存储过程,应将EXEC授予该过程。