T-SQL一次性撤銷sql server中所有表的權限

我們如何撤銷對所有表的特定權限？

我試過（T-SQL）：

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

但是什麼也沒有發生。所有表仍然具有選擇權限。

我可以一個一個

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

一項一項工作正常。

如何一次性撤銷特定模式的所有表的權限？

我需要為此建構一個游標嗎？

你可以試試

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

而不是列印

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

怎麼做其中之一：

1. DENY SELECT ON SCHEMA::DBO TO PUBLIC（公共或登錄名或登錄名集）
2. ALTER ROLE [db_denydatareader] ADD MEMBER [your login]

兩者都為我工作，完整的測試如下：

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

引用自：https://dba.stackexchange.com/questions/243489