Monday, March 13, 2006

In SQL Server 2005 Control permission is special cased. Control grant chains are always rooted at the owner unless you explicitly use an AS clause. This is to prevent orphaned grant arcs for other permissions that Control covers.
Example:
1. You give User1 Control permission on an object with GRANT OPTION;
2. User1 give User2 Control permission on the object;
3. You REVOKE Control permission on the object from User1 with CASCADE option.
4. User2 still have Control permission on the object.

Grant Control on T to usr1 WITH GRANT OPTION
go
Execute as user = 'usr1'
go
Grant Control on T to usr2
go
REVERT
go
Revoke Control on T from usr1 Cascade
go

-- Usr2 still has control permission on T. To be able to revoke control permission from all users
-- who's been given this permission by User1.

-- User1 should be specified explicitly when granting permission to User2:
Grant Control on T to usr2 AS usr1
go

No comments: