Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Let's suppose a user, "Bob", gets SELECT permission from John and Bob also gets SELECT permission from another user, Joe, but this time with GRANT option.

What happens if someone revokes the select permission from Bob with cascade? Will the select permission be removed from Bob's account and all the accounts that Bob gave the permission to (even if they had it before from somewhere else like a group?)? If so, will the whole group loose the Select permission or just the one user that had the Select permission from Bob too ?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

No, they will not lose the permissions.

If a GRANT is performed for the same permission by multiple principals SQL Server will keep track of that. That is, it'll store the permission multiple times differentiated by which principal it was that performed the GRANT.

So, say Admin runs these:

GRANT SELECT ON OBJECT::MyDb.dbo.MyTable to UserA WITH GRANT OPTION  
GRANT SELECT ON OBJECT::MyDb.dbo.MyTable to UserB

Then UserA runs the 2nd statement:

GRANT SELECT ON OBJECT::MyDb.dbo.MyTable to UserB

And finally Admin runs this:

REVOKE SELECT ON OBJECT::MyDb.dbo.MyTable TO UserA CASCADE

UserB retains SELECT permissions on MyTable because it was gratned by Admin in the first batch.

In your case the whole group will not lose access, only the one user that Bob granted SELECT to, assuming that nobody else also granted the same permission.

The one user will retain permissions, though, if they are part of a Windows group or a database role that also has the permissions. That is, as long as the permission wasn't granted by Bob.

share|improve this answer
    
But he is still part of the group that had the Select permission.. Why wouldn't he still have the Select permission from the group ? –  pi012 Jul 5 '11 at 22:25
    
Are you saying that you have a database role that was also granted access and that user is also in that role? Or is this a Windows group? –  squillman Jul 5 '11 at 22:29
    
Is there a difference ? –  pi012 Jul 5 '11 at 22:31
    
Yes, definitely a difference. –  squillman Jul 5 '11 at 22:36
    
Not so much with the GRANT, but with other things that could potentially affect that. –  squillman Jul 5 '11 at 22:37
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.