无法撤销其他用户授予的权限

2021-09-10 00:00:00 tsql sql-server-2008 sql-server

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)2011 年 9 月 21 日 22:45:45版权所有 (c) 1988-2008 Microsoft CorporationWindows NT 6.1(内部版本 7601:Service Pack 1)上的企业版(64 位)

我的数据库中有一个存储的 proc 和一个 user1，该用户 1 将该 proc 的 exec 权限授予其他 user2.所以 user1 是授权人.

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.

现在我(作为 SA)尝试撤销 user2 的此权限.

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

revoke exec on sp from user2

我明白

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')

我做错了什么?

推荐答案

GRANT 和 REVOKE 是非常复杂的结构命令，有时很难跟踪所有小细节.如果您可以发布您运行的查询以授予用户执行权限，那将会很好，这两个用户都是任何 ServerRole 的成员吗?您是否使用带有 GRANT 选项的 GRANT 命令?

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 ?

从您的描述和其他评论中，我看到 User_1 是授予者，如果它是 DB_Owner，则意味着它拥有所有权限.那么 User_2 呢?它是任何服务器角色的成员吗?

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?

试试下面的这个脚本.它只是演示了 SQL 2008-R2-SP1 上的 GRANT 和 REVOKE 预期行为.在这个例子中，dbo 是授权者，user1 是被授权者.

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.

查看输出图像 输出的前两行显示了正在执行该程序的正确用户.但是在对 User_1 执行撤销后，我们收到一条错误消息.

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.

此外，在 GRANT 之后，我可以在 sysprotect ut 中看到 recird 撤销后我没有看到 user1 的任何记录.

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

1

我找到了有趣的原因.这是 REVOKE 语句的语法.检查下面的例子.在这个例子中，user1 是数据库所有者和授予者，而 user2 是被授予者.

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.

检查输出.下图.使用通用语法REVOKE EXEC ON SP FROM USER2"更新 sysprotect 但实际上并不撤销权限.但是如果我们使用另一个语法REVOKE EXEC ON OBJECT::SP From user2"，那么它会实际撤销权限，我们可以在下面附加的结果中看到这一点.

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.

设置无计数

    --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

相关文章