Cannot revoke permission granted by other user

Question

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

I'v got a stored proc in my database and a user1 that granted exec permission on that proc to other user2. So user1 is grantor.

Now I (as SA) try to revoke this permission from user2.

revoke exec on sp from user2

I get

Command(s) completed successfully.

But still I can see granted permissions!

SELECT object_name(id), user_name(uid) FROM sysprotects WHERE grantor = USER_ID('user1')

What am I doing wrong?

Answer 1

Have you tried:

revoke exec on sp from user2 as user1

• http://technet.microsoft.com/en-us/library/ms187719.aspx

Answer 2

Have you included the column action from sysprotects? Maybe there's an addidional permission which have been granted to user1?!

;with "action"
as
(
select "actionid" = 26, "description"='REFERENCES' union all 
select 178, 'CREATE FUNCTION' union all 
select 193, 'SELECT' union all 
select 195, 'INSERT' union all 
select 196, 'DELETE' union all 
select 197, 'UPDATE' union all 
select 198, 'CREATE TABLE' union all 
select 203, 'CREATE DATABASE' union all 
select 207, 'CREATE VIEW' union all 
select 222, 'CREATE PROCEDURE' union all 
select 224, 'EXECUTE' union all 
select 228, 'BACKUP DAABASE' union all 
select 233, 'CREATE DEFAULT' union all 
select 235, 'BACKUP LOG' union all 
select 236, 'CREATE RULE'
)
select object_name(id), user_name(uid), A.description FROM sysprotects SP 
inner join "action" A
    on A."actionid" = SP."action"
where grantor = USER_ID('user1')

Answer 3

GRANT and REVOKE are very complex structure commands and sometimes it becomes difficult to track all small details. It will be nice if you can post the Queries you ran to Grant the Execute permission on user, are these both user member of any ServerRole ? did you use GRANT command with GRANT option ?

from your description and other comments i see User_1 is Grantor and if it is DB_Owner that means it has all privileges. so what about User_2 ? is it member of any server role?

try this script below. it is just demonstrating GRANT and REVOKE expected behavior on SQL 2008-R2-SP1. in this example dbo is granter and user1 is grantee.

see the output image first two line of output is showing the right User who is executing the procedure. but after Doing Revoke on User_1, we have a error message.

Also, After GRANT i can see the recird in sysprotect ut after revoke i do not see any record for user1.

    --Creating a Procedure
    IF Object_ID('Pr_TestUserPermission') IS NOT NULL
        DROP PROCEDURE Pr_TestUserPermission
    GO
    CREATE PROC Pr_TestUserPermission
    AS
    BEGIN
        PRINT 'This Procedure is currently executed under user ' + USER_NAME()
    END
    GO

    --Granting another User Exec permission of this proc
    GRANT EXEC ON Pr_TestUserPermission TO user1
    SELECT object_name(id), user_name(uid) FROM sysprotects WHERE user_name(uid) = 'User1'

    --Executing the procedure as Original User
    EXECUTE Pr_TestUserPermission

    --Change the Executer User Context to User1 and then Try to Call same Procedure
    EXECUTE AS LOGIN='User1'
    EXECUTE Pr_TestUserPermission
    REVERT


    --Revoke the Grant permission
    REVOKE EXEC ON Pr_TestUserPermission FROM user1
    SELECT object_name(id), user_name(uid) FROM sysprotects WHERE grantor = USER_ID('user1')


    --Change the Executer again back to User1 and Try to Call same Procedure
    EXECUTE AS LOGIN='User1'
    EXECUTE Pr_TestUserPermission
    REVERT 

EDIT:1

I found interesting reason for this. it's the syntax of REVOKE statement. check the below example. in this example user1 is db owner and Granter while user2 is grantee.

check the output. image below. using the common syntax "REVOKE EXEC ON SP FROM USER2" that updates the sysprotect but does not actually revoke the permission. but if we use the another syntex "REVOKE EXEC ON OBJECT::SP From user2" then it does the actual revoke of permission and we can see that in the result attached below.

SET NOCOUNT ON

    --Creating a Procedure
    IF Object_ID('Pr_TestUserPermission2') IS NOT NULL
        DROP PROCEDURE Pr_TestUserPermission2
    GO
    CREATE PROC Pr_TestUserPermission2
    AS
    BEGIN
        PRINT 'This Procedure is currently executed under user ' + USER_NAME()
    END
    GO

    --Granting another User Exec permission of this proc
    GRANT EXEC ON Pr_TestUserPermission2 TO user2
    SELECT object_name(id), user_name(uid) FROM sysprotects WHERE user_name(uid) = 'User2'

    --Executing the procedure as Original User
    EXECUTE Pr_TestUserPermission2

    --Change the Executer User Context to User1 and then Try to Call same Procedure
    EXECUTE AS USER='user2'
    EXECUTE Pr_TestUserPermission2
    REVERT


    --Revoke the Grant permission using common syntex
    REVOKE EXECUTE ON Pr_TestUserPermission FROM user2
    SELECT object_name(id), user_name(uid) FROM sysprotects WHERE grantor = USER_ID('user2')


    --Change the Executer again back to User1 and Try to Call same Procedure
    EXECUTE AS USER='user2'
    EXECUTE Pr_TestUserPermission2
    REVERT


    --Revoke the Grant permission using another sytex
    REVOKE EXECUTE ON OBJECT::dbo.Pr_TestUserPermission2 FROM user2
    SELECT object_name(id), user_name(uid) FROM sysprotects WHERE grantor = USER_ID('user2')

    --Change the Executer again back to User1 and Try to Call same Procedure
    EXECUTE AS USER='user2'
    EXECUTE Pr_TestUserPermission2
    REVERT