SQL Server REVOKE requires CASCADE

Question

I have the following graph:

• B granted select on D and D granted select on E and E granted select on G.
• B has grant select with grant option and has granted select D with grant option.
• D has grant select with grant option and has granted select E with grant option.
• E has grant select with grant option and has granted select G with grant option.

When I try to revoke the GRANT SELECT on D, I get an error that requires the use of CASCADE.

Code:

EXECUTE AS USER = 'userB';
GO
REVOKE GRANT OPTION FOR SELECT ON EMP FROM userD;
GO
REVERT;

Error:

To revoke or deny grantable privileges, specify the CASCADE option.

What I need is to remove the GRANT SELECT without removing the GRANT SELECT from E and G.

Answer 1

When you grant permissions to a user using the WITH GRANT OPTION clause, then subsequently revoke that permission, SQL Server will prevent the revoke unless you explicitly add the CASCADE option to the REVOKE ... statement.

This prevents a security risk whereby you grant WITH GRANT OPTION to a user, who subsequently grants rights to others unbeknownst to you. You then revoke the rights to that user, but the damage has already been done. This error message is thrown when you attempt to revoke permissions that have been assigned with the grant option, unless you use the CASCADE option:

Msg 4611, Level 16, State 1, Line 44

To revoke or deny grantable privileges, specify the CASCADE option.

In order to revoke permissions that have been granted using the WITH GRANT OPTION clause, you must revoke the permission using the CASCADE option. This also removes the revoked rights from all users that have been granted the rights by the user with the GRANT OPTION. If you want those other users to retain the rights granted by the user with the GRANT OPTION, you must then manually assign those rights explicitly to those other users.

This code below is a simple repro showing the symptoms, and how to deal with it.

First, create an object we can assign rights to:

IF OBJECT_ID('dbo.TestSelect') IS NULL
CREATE TABLE dbo.TestSelect
(
    t INT
);

Create three users:

IF NOT EXISTS (
    SELECT 1 
    FROM sys.database_principals dp 
    WHERE dp.name = 'u1')
CREATE USER u1 WITHOUT LOGIN;

IF NOT EXISTS (
    SELECT 1 
    FROM sys.database_principals dp 
    WHERE dp.name = 'u2')
CREATE USER u2 WITHOUT LOGIN;

IF NOT EXISTS (
    SELECT 1 
    FROM sys.database_principals dp 
    WHERE dp.name = 'u3')
CREATE USER u3 WITHOUT LOGIN;

Grant SELECT WITH GRANT OPTION to user u1:

GRANT SELECT ON dbo.TestSelect TO u1 WITH GRANT OPTION;

Grant SELECT WITH GRANT OPTION to user u2 as user 'u1' :

EXECUTE AS USER = 'u1';

GRANT SELECT ON dbo.TestSelect TO u2 WITH GRANT OPTION;

REVERT

Grant SELECT WITH GRANT OPTION to user u3 as user 'u2' :

EXECUTE AS USER = 'u2';

GRANT SELECT ON dbo.TestSelect TO u3 WITH GRANT OPTION;

REVERT

Ensure we are running as the initial user (in my case dbo)

SELECT USER_NAME();

Attempt to remove the SELECT rights from user u1 without using the CASCADE option, which will fail:

REVOKE SELECT ON dbo.TestSelect FROM u1;
--Msg 4611, Level 16, State 1, Line 44
--To revoke or deny grantable privileges, specify the CASCADE option.

Add the CASCADE option, which works:

REVOKE SELECT ON dbo.TestSelect FROM u1 CASCADE;

See if the other users have access to the test table (they don't since we've revoked access using the CASCADE option):

EXECUTE AS USER = 'u2';

SELECT *
FROM dbo.TestSelect;
--Msg 229, Level 14, State 5, Line 51
--The SELECT permission was denied on the object 'TestSelect', database 'Test', schema 'dbo'.

Revert back to the 'dbo' user, so we can grant SELECT on the test object to the remaining users:

REVERT -- back to 'dbo'

GRANT SELECT ON dbo.TestSelect TO U2;
GRANT SELECT ON dbo.TestSelect TO U3;

Confirm the other users have access to the test object:

EXECUTE AS USER = 'u2';

SELECT *
FROM dbo.TestSelect;

REVERT -- back to 'dbo'

EXECUTE AS USER = 'u3';

SELECT *
FROM dbo.TestSelect;

REVERT -- back to 'dbo'

Clean up the test object and users:

DROP TABLE dbo.TestSelect
DROP USER u3;
DROP USER u2;
DROP USER u1;

Answer 2

It seems to be something to be corrected by Microsoft. The only way I see is to drop the impacted users and recreate them with the needed permissions ...

@++ ;)