0

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.

1
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
5
  • "REVOKE" wouldn't guarantee that users can not access the objects as they could gain the access through other groups or database Role. You may want to explicitly DENY the permissions to the users. DENY does not take effect on SYSAdmins or object owner.
    – Unkush
    Jul 30 '20 at 17:03
  • @Unkush good point - answer updated
    – SEarle1986
    Aug 2 '20 at 13:52
  • Thank you everyone for your response. I tried the above sql, and it's working for most, but logins with db_datareader role are not included. your suggestion to limit at database level, will that fall for all the tables? or just selected tables? example" i want to limit 100 tables out of 500 tables. so i do revoke/deny to the list, at the same time, i also deny select to public on database level. what happens to the 490 tables?> Aug 6 '20 at 4:00
  • @coolmind1414 have updated the answer
    – SEarle1986
    Aug 6 '20 at 11:22
  • Thank you @SEarle1986. Really appreciate it. Aug 10 '20 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.