如何找出存储过程中的权限/授权何时以及何时更改

There is a stored procedure in SQL Server 2005 in which users claim execute permission was removed as they can no longer execute the stored procedure.

SQL Server 2005中存在一个存储过程,其中用户声明执行权限已被删除,因为它们无法再执行存储过程。

I tried to find it in trace files, but i can't find an entry for this stored procedure

我试图在跟踪文件中找到它,但我找不到这个存储过程的条目

My question is is there other way to find out who and when permission/grants of this stored procedure was changed?

我的问题是,有没有其他方法可以找出谁更改以及何时更改此存储过程的许可/授权?

4 个解决方案

#1

Restore a backup?

还原备份?

#2

Unfortunately no. You have to audit (via DDL triggers for example) the actual REVOKE or DENY statement.

很不幸的是,不行。您必须审核(例如通过DDL触发器)实际的REVOKE或DENY语句。

However, if the proc was dropped and recreated that there is no permission change to audit. You can query sys.objects to get create_date and modify_date to find out if this happened.

但是,如果删除了proc并重新创建了审核没有权限更改。您可以查询sys.objects以获取create_date和modify_date以查明是否发生了这种情况。

There are other options, such as the login removal so even though permissions have not changed, the users are no longer eligible to execute the code. Or the NT group changed if using Windows authentication. An explicit REVOKE/DENY or DROP/CREATE may not be the obvious answer.

还有其他选项,例如登录删除,因此即使权限未更改,用户也不再有资格执行代码。或者,如果使用Windows身份验证,NT组也会更改。显式的REVOKE / DENY或DROP / CREATE可能不是明显的答案。

Edit, based on comment:

根据评论进行修改:

Your question said "..users claim execute permission was removed...". As well as explicit stored proc permisson changes, the environment may have changed. That is, what if:

你的问题说“..用户声称执行权限已被删除......”。除了显式存储的proc permisson更改外,环境可能已更改。也就是说,如果:

users were dropped from the group

用户从该组中删除

the login was dropped and recreated without user mapping in the database

登录被删除并重新创建,而无需在数据库中进行用户映射

the group policy changed so the NT group can no longer access the SQL Server (they need "Allow login from network"-ish)

组策略已更改,因此NT组无法再访问SQL Server(他们需要“允许从网络登录”-ish)

Have stored proc rights changed, or have how user get to the stored proc changed?

存储过程权限是否已更改,或者用户如何更改存储过程?

#3

select P.permission_name,  
P.state_desc,
U.name GranteeName, 
U2.name GrantorName, 
T.*
from sys.database_permissions P 
JOIN sys.objects  T  ON P.major_id = T.object_id 
JOIN sysusers U  ON U.uid = P.grantee_principal_id
JOIN sysusers U2 ON U2.uid = P.grantor_principal_id
ORDER by T.modify_date  desc

Unfortunately, the 'who' is typically 'dbo'...

#4

Select      memb.Name As UserName,
            prin.Name As RoleName,
            perm.permission_name As PermissionType,
            schm.name As SchemaName,
            objt.Name As ObjectName,
            perm.state_desc As PermissionState,
            memb.Create_Date ,
            memb.Modify_Date
from        sys.database_principals memb
Left Join   sys.database_role_members rolm 
            on rolm.Member_principal_id = memb.Principal_id  
Left Join   sys.database_principals prin 
            on rolm.Role_principal_id = prin.Principal_id
Left Join   sys.database_permissions perm 
            on memb.Principal_id = perm.grantee_principal_id  
Left Join   sys.all_objects objt 
            on perm.Major_Id = objt.Object_Id
Left Join   sys.Schemas schm 
            on objt.schema_id = schm.schema_id
Order By    memb.Name, 
            prin.Name, perm.permission_name, 
            objt.Name, perm.state_desc;

#1