1

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?

  • What happens with DENY SELECT ON SCHEMA::dbo TO [public];? Did you try this with other logins/roles other than public? What's wrong with just adding public to db_denydatareader (assuming other schemas off limits too)? Did you check that SELECT didn't work, or is "nothing happens" just that some UI doesn't explicitly reflect all of the individual deny/revoke properties per table? SQL Server isn't going to do that; it knows you are going to add more tables and views later, so if you specify something at the schema level, it's only going to be validated/reflected at the schema level. – Aaron Bertrand Jul 22 '19 at 23:27
  • Hi @AaronBertrand, a) Is not what I want. b)Yes. c)Don't want to deny. d) 'nothing happens' means that no changes were made. Yes it seems that by revoking the SELECT permission ON SCHEMA wont wont affects the permissions to all tables from that schema. I thought it could behave in a CASCADE effect. – ePascoal Jul 23 '19 at 3:03
  • It doesn’t cascade like that, as I already explained. When the grocery store closes at night, they lock the doors, they don’t put locks on every individual cooler and freezer and aisle and shelf... can you explain exactly why you think you need revoke instead of deny? How are you defining "no changes were made"? What changes were you expecting, and where were you looking exactly? – Aaron Bertrand Jul 23 '19 at 3:35
  • Hi @AaronBertrand, I was looking for a more elegant way to revoke permissions in one go to all tables in a certain schema. Doesn't seem to be an awkard question as other technologies have a way to do it. Postgres has this functionality: postgresql.org/docs/9.1/sql-revoke.html. And also there was a similar discussion about Oracle : dba.stackexchange.com/questions/120973/… In fact you may lock every individual cooler and freezer on a grocery store in one go that will only depend on what technology you are using. – ePascoal Jul 24 '19 at 7:04
2

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]'
1

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

enter image description here

select * from dbo.t1 order by the_name desc

enter image description here

--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

enter image description here

--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

enter image description here

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

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'?

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.