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