CODEWITHSUNDEEP
sql-server
ThatDataGuy
ThatDataGuy

Reputation: 2109

How can I revoke all the permissions a role has in sql server?

I have a role, it has some select perms on various tables. I would like to remove all the select permissions that the role has across all tables. Eg,

revoke all from my_role_name;

But this doesn't seem to work. How can I do this?

Upvotes: 2

Views: 2547

Answers (1)

Martin Smith
Martin Smith

Reputation: 453608

I just had the need to do this.

You can use something along the lines of the below

DECLARE @RevokeScript NVARCHAR(MAX);
DECLARE @PrincipalName SYSNAME = 'my_role_name'

SELECT @RevokeScript = STRING_AGG(CAST('REVOKE ' + permission_name
                                       + CASE class_desc
                                           WHEN 'OBJECT_OR_COLUMN' THEN ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(major_id)) + '.' + QUOTENAME(OBJECT_NAME(major_id))
                                           WHEN 'SCHEMA' THEN ' ON  SCHEMA::' + SCHEMA_NAME(major_id)
                                           WHEN 'DATABASE' THEN ''
                                         END
                                       + ' TO ' + QUOTENAME(@PrincipalName) COLLATE SQL_Latin1_General_CP1_CI_AS AS NVARCHAR(MAX)), ';')
FROM   sys.database_permissions AS pe
WHERE  pe.grantee_principal_id = DATABASE_PRINCIPAL_ID (@PrincipalName);

PRINT @RevokeScript

EXEC (@RevokeScript)

Upvotes: 2

Related Questions