T-SQLRevoke permissions to all tables in the sql server in one go

Question

How can we revoke a certain permission to all tables?

I've tried (T-SQL):

REVOKE SELECT on schema::DBO TO [public] AS [dbo]

But nothing happens.. All tables still have Select permission.

I can take one by one

REVOKE SELECT on  dbo.table TO [public] AS [dbo]

One by one works ok.

How to revoke permissions from all tables of a specific schema in one go?

Do I need to build a cursor for this?

Answer 1

you can try

EXEC sp_MSforeachtable @command1 = 'PRINT (''REVOKE SELECT on ? TO [Role] AS [dbo]'')'

and instead of print

EXEC sp_MSforeachtable @command1 = 'REVOKE SELECT on ? TO [Role] AS [dbo]'

Answer 2

what about doing one of these:

1. DENY SELECT ON SCHEMA::DBO TO PUBLIC (public or a login or set of logins)
2. ALTER ROLE [db_denydatareader] ADD MEMBER [your login]

Both worked for me and the complete test is below:

use master
go

--create a login for testing
CREATE LOGIN Radhe WITH PASSWORD='HareKrishna001!', DEFAULT_DATABASE=master;
GO

--create a database just for this testing
create database test_permissions
go
use test_permissions
go

--adding the user to the database
IF NOT EXISTS (SELECT * from sys.sysusers WHERE name='Radhe')  
CREATE USER [Radhe] FOR LOGIN [Radhe]  WITH DEFAULT_SCHEMA = [dbo] 

--create a table so that we can test the permissions
create table dbo.t1 (id int identity(1,2) not null, the_name nvarchar(108) not null)

--adding some data to the table
insert into t1(the_name) values ('Balarama')
insert into t1(the_name) values ('Caitanya')
insert into t1(the_name) values ('Gauranga')
insert into t1(the_name) values ('Govinda')

--cheking the data inside the table
select * from dbo.t1 order by the_name desc

--now I will run as my user and it should fail because I have not granted this user any permissions
EXECUTE AS LOGIN='RADHE'

--checking who I am at the moment - I am my testing login
DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT  [THE_SERVER]= @@SERVERNAME
        ,[DB_NAME] =DB_NAME()
        ,[@USER]=@USER 
        ,[SUSER_SNAME()]=SUSER_SNAME()
        ,[SYSTEM_USER]=SYSTEM_USER
        ,[USER_NAME()]=USER_NAME() 
        ,[CURRENT_USER]=CURRENT_USER
        ,[ORIGINAL_LOGIN()]=ORIGINAL_LOGIN()
        ,[USER]=USER
        ,[SESSION_USER]=SESSION_USER

select * from dbo.t1 order by the_name desc

--now I want to be me again
REVERT

--and I grant select to my login
GRANT SELECT ON DBO.T1 TO [RADHE]

--and when I run the select as my login
EXECUTE AS LOGIN='RADHE'

--because he has the select permission
-- I get the following
select * from dbo.t1 order by the_name desc

--reverting to be me again so that I can apply the restricting permissions
REVERT

--BOTH OF THESE WORK FINE, I prefer the second one because it is clearer:

--DENY SELECT ON SCHEMA::DBO TO PUBLIC

USE [test_permissions]
GO
ALTER ROLE [db_denydatareader] ADD MEMBER [Radhe]
GO

--now the select should be restricted 
EXECUTE AS LOGIN='RADHE'

select * from dbo.t1 order by the_name desc

--and don't forget to revert back and be yourself
REVERT

Answer 3

The Cursor approach:

DECLARE @statement NVARCHAR(100) -- statement  
DECLARE @table SYSNAME -- Table name 


DECLARE db_cursor CURSOR FOR 
SELECT t.name
FROM sys.tables t
INNER JOIN sys.schemas s
ON s.schema_id = t.schema_id
WHERE s.name = 'dbo' -- or just test for schema_id = 1

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @table  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    SET @statement = 'REVOKE SELECT ON dbo.' + QUOTENAME(@table) + ' TO [public] AS [dbo]'
    EXECUTE @statement

    FETCH NEXT FROM db_cursor INTO @table 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 

There is anything 'out of the box'?