score:2

Accepted answer

Yes, the DENY is the way to go here. Yes, if the table is used in view the user will be able to query it.

The following example code demonstrates it:

--DROP USER IF EXISTS [StackOverflow];
--DROP TABLE IF EXISTS [dbo].[Data];
--DROP TABLE IF EXISTS [dbo].[DataDeny];
--DROP VIEW IF EXISTS [dbo].[vw_Data];

CREATE USER [StackOverflow] WITHOUT LOGIN;

EXEC sp_addrolemember @rolename = 'db_datareader'
                     ,@membername = 'StackOverflow';

GO

CREATE TABLE [dbo].[Data]
(
    [column] INT
);

INSERT INTO [dbo].[Data]
VALUES (1);

GO

CREATE TABLE [dbo].[DataDeny]
(
    [column] INT
);

INSERT INTO [dbo].[DataDeny]
VALUES (2);

GO

CREATE VIEW [dbo].[vw_Data]
AS
SELECT [column]
FROM [dbo].[Data]
UNION ALL
SELECT [column]
FROM [dbo].[DataDeny];

GO

DENY SELECT ON [dbo].[DataDeny] TO [StackOverflow]

GO

EXECUTE AS USER  = 'StackOverflow';

SELECT [column] AS [read]
FROM [dbo].[Data];

SELECT [column] AS [not_Read]
FROM [dbo].[DataDeny]

SELECT [column] AS [read]
FROM [dbo].[vw_Data];

REVERT;

enter image description here


More questions

More questions with similar tag