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;
More questions
- MSSQL - Grant Select rights to all tables except one
- How to select all the columns of a table except one column?
- How to select all columns in sql except one column?
- duplicate row -- select all columns except one
- Remove all user's rights to all tables in one statement
- How to get data from 2 rows which has same data in all columns except one in MSSQL
- Select all nodes except one in XML using XQuery
- How to deny write premissions to a user for all tables except one
- Using the OVER clause in T-SQL to SELECT DISTINCT on all columns except one
- Select all tables with at least one row of data inserted
- SELECT One column from multiple tables MSSQL
- How to select all records from one table that do not exist in another table?
- Select all empty tables in SQL Server
- Select all columns from all tables in SQL Server 2008
- Inner Join Tables But Select From One Table Only
- Select from multiple tables in one call
- select all rows except top row
- Select all columns but group by only one in linq
- select all columns with one column has different value
- Delete all tables in SQL Server database except few
- How do I combine multiple tables into one new table? All of the columns headers are the same and in the same order
- How to select all tables with a particular name in database
- Add multiple combined PHP form data from multiple foreign MSSQL tables to one primary table
- How to delete all rows by group except the first and the last one in SQL Server?
- How to INSERT into multiple tables from one SELECT statement
- Is it better to do multiple selects from multiple tables or 1 select of all your data from all the tables?
- How to Grant REFERENCES permission to all tables
- How to drop all tables except 1
- Performance consideration: Spread rows in multiple tables vs concentrate all rows in one table
- Select all from one table where some columns match another select
More questions with similar tag
- PHP --> SQL Server + Excel file upload from a specific row
- SQL Server: Count how big a column value is compared to the others
- See if all records in the same group are of accepted types
- IIS SQL Server: Connection Pool: Why are new connections made instead of reusing existing connections?
- How can I limit the substring to extract text between two spaces in SQL Server?
- Simple ISEMPTY() function in SQL Server throws a non-boolean type error
- Sql Server 2005 SSIS/Agent - Query status of a job
- Converting Nvarchar(MAX) to DATETIME Datatype SQL Server query
- Why DROP TABLE and then re-CREATE TABLE?
- Cannot view tables on AWS RDS SQL Server Express
- Temporary table using Transact sql
- SQL query to select equal or less than or greater than
- Extract documentation strings from SQL Scripts
- Short way to serialize datetime with marshmallow
- Filter categories in which (in another column) values differ
- SQL query takes too long
- SQL Server - Retrieve two rows in a unique row using alias
- Connect localdb from production database
- TSQL - Only execute a line if run manually (not in job)
- What is the purpose of putting an 'N' in front of function parameters in TSQL?
- Issue referencing a portable class library from a SQL Server database project
- save DataTable to database
- How to select unrelated entities with a single query
- filling dataset using data adapter
- Delete from one table in my database(SQL server) is slow
- How can I tell that SQL Server is started and ready to use?
- How to query only rows that will exceed to the max quantity
- How to get the status of the backup database running through dynamic sql?
- Cannot see statement text in Sql Profiler
- SQL error in subquery
Source:
stackoverflow.com