Restrict use of tables

Question

I am looking to restrict SELECT/INSERT for the 60-80 tables in the database. The database has very complex security structure, and want to restrict INSERT/SELECT for all users (and except SysAdmin, no other login should have access to those tables).

is that possible? thank you in advance.

Answer 1

By default, SELECT and INSERT access will not be granted. You can REVOKE any SELECT / INSERT perms as follows:

USE MyDatabase;

DECLARE @sql NVARCHAR(MAX) = '';

SELECT  @sql +=
        'REVOKE ' + p.[permission_name] COLLATE DATABASE_DEFAULT + ' ON [' + s.[name] COLLATE DATABASE_DEFAULT  + '].[' + o.[name] COLLATE DATABASE_DEFAULT + '] FROM [' + pr.[name] COLLATE DATABASE_DEFAULT + ']'
FROM    sys.database_permissions p
        JOIN sys.database_principals pr
            ON p.grantee_principal_id = pr.principal_id
        JOIN sys.objects o
            ON o.object_id = p.major_id
        JOIN sys.schemas s
            ON s.schema_id = o.schema_id
WHERE   p.[permission_name] IN ('SELECT','INSERT');
-- you can also filter on o.name to restrict to specific objects
-- and pr.name for specific prinicipals

EXEC sp_executesql @command = @sql;

As Unkush said in the comment, this will only REVOKE any existing access to that explicitly granted. access can also be implictly granted via built the built in db_datareader role and other avenues such as db_owner group and schema ownership.

To harden this further, you can place a deny on the public role (which all users fall into but this won't affect sysadmins) at the database level as follows:

USE MyDatabase;
DENY SELECT ON DATABASE:: MyDatabaseTO [public];

Here is a re-producible test:

First, set up the test db:

USE [master];

/* Create our test db*/
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'PermissionsTest')
BEGIN
    ALTER DATABASE PermissionsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PermissionsTest;
END;

CREATE DATABASE PermissionsTest;
GO

USE PermissionsTest;

/* Create some tables*/
CREATE TABLE MyTable1
(
    a INT,
    b INT
);

CREATE TABLE MyTable2
(
    a INT,
    b INT
);

CREATE TABLE MyTable3
(
    a INT,
    b INT
);

CREATE TABLE MyTable4
(
    a INT,
    b INT
);

CREATE TABLE MyTable5
(
    a INT,
    b INT
);

CREATE TABLE MyTable6
(
    a INT,
    b INT
);

/* set up some users */
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin1') DROP LOGIN MyLogin1;
CREATE LOGIN MyLogin1 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser1 FOR LOGIN MyLogin1;

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin2') DROP LOGIN MyLogin2;
CREATE LOGIN MyLogin2 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser2 FOR LOGIN MyLogin2;

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin3') DROP LOGIN MyLogin3;
CREATE LOGIN MyLogin3 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser3 FOR LOGIN MyLogin3;

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin4') DROP LOGIN MyLogin4;
CREATE LOGIN MyLogin4 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser4 FOR LOGIN MyLogin4;

/* grant some explicit permissions */
GRANT SELECT ON MyTable1 TO MyUser1,MyUser2;
GRANT SELECT ON MyTable2 TO MyUser1,MyUser2;
GRANT SELECT ON MyTable3 TO MyUser1,MyUser2;

/* grant some permissions via a custom role */
CREATE ROLE MyRole;
ALTER ROLE MyRole ADD MEMBER MyUser2;
ALTER ROLE MyRole ADD MEMBER MyUser3;

GRANT SELECT ON MyTable4 TO MyRole;
GRANT SELECT ON MyTable5 TO MyRole;

/* add a user to the standard db role */
ALTER ROLE db_datareader ADD MEMBER MyUser4;

Then we can see how these permissions work:

EXECUTE AS LOGIN = 'MyLogin1'
    SELECT * FROM MyTable1 /* should return 0 rows (explicit permision)*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin2'
    SELECT * FROM MyTable1 /* should return 0 rows (explicit permission)*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (via MyRole membership)*/
REVERT

EXECUTE AS LOGIN = 'MyLogin4'
    SELECT * FROM MyTable1 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable6 /* should return 0 rows (db_datareader)*/
REVERT

Then we can run the dynamic revoke :

USE PermissionsTest;

DECLARE @sql NVARCHAR(MAX) = '';

SELECT  @sql +=
        'REVOKE ' + p.[permission_name] COLLATE DATABASE_DEFAULT + ' ON [' + s.[name] COLLATE DATABASE_DEFAULT  + '].[' + o.[name] COLLATE DATABASE_DEFAULT + '] FROM [' + pr.[name] COLLATE DATABASE_DEFAULT + ']'
FROM    sys.database_permissions p
        JOIN sys.database_principals pr
            ON p.grantee_principal_id = pr.principal_id
        JOIN sys.objects o
            ON o.object_id = p.major_id
        JOIN sys.schemas s
            ON s.schema_id = o.schema_id
WHERE   p.[permission_name] IN ('SELECT','INSERT');

EXEC sp_executesql @command = @sql;

And test (this will leave the db_datareader perms in place)

EXECUTE AS LOGIN = 'MyLogin1'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin2'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin4'
    SELECT * FROM MyTable1 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable6 /* should return 0 rows (db_datareader)*/
REVERT

We can then either place a deny on the entire database (a deny is the most robust way as there are various other avenues users can get SELECT permissions such as db_datareader, schema ownership, db_owner etc)

DENY SELECT ON DATABASE:: PermissionsTest TO [public];

Alternatively, you can supply a more granular DENY at a schema or table level:

DENY SELECT ON TABLE:: MyTable1 TO [public]

Running the Test again:

EXECUTE AS LOGIN = 'MyLogin1'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin2'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin4'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable6 /* should return 0 rows (db_datareader)*/
REVERT