В ситуации когда пользователь должен видеть только определенные данные из таблицы или не хочется заморачиваться с правами на таблицу
пригодится использование механизма цепочек владения.
Если кратко то в случае когда объектами владеет один и тот же владелец то включается механизм цепочки владения который позволяет исключить проверку прав для входящих в процедуру или представление объектов.
Например хотим создать отдельную схему и дать доступ пользователю только на объекты данной схемы. Подробнее можно почитать тут
Создаем схему указываем владельца dbo, т.к будем использовать в представлениях и процедурах объекты данной схемы, таким образом включается цепочка наследования.
! Необходимо указать правильного владельца сразу иначе цепочка оборвется и придется пересоздавать схему.
CREATE SCHEMA [MDM] AUTHORIZATION [dbo]
Далее создаем представление, которое использует например таблице TEST в схеме dbo
CREATE VIEW MDM.TESTVIEW AS
SELECT *
FROM dbo.TEST
Даем права нашему пользователю на селект и грант, без гранта работать не будет.
GRANT SELECT , VIEW DEFINITION ON MDM.TESTVIEW TO [TestUser] WITH GRANT OPTION
Проверяем доступ к представлению и убеждаемся что все работает
P\S скриптом ниже можно проверить все права пользователя
EXECUTE AS LOGIN = N'TestUser';
GO
DECLARE @login NVARCHAR(256), @user NVARCHAR(256);
SELECT @login = login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
SELECT @user = d.name
FROM sys.database_principals AS d
INNER JOIN sys.server_principals AS s
ON d.sid = s.sid
WHERE s.name = @login;
SELECT u.name, r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
INNER JOIN sys.database_principals AS u
ON u.principal_id = m.member_principal_id
WHERE u.name = @user;
SELECT class_desc, major_id, permission_name, state_desc
FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(@user);
GO
REVERT;
