CODEWITHSUNDEEP
sqlsql-server-2005
titanium
titanium

Reputation: 367

how to find out who and when permissions/grants in stored procedure was changed

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.

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?

Upvotes: 1

Views: 8790

Answers (4)

Tony Price
Tony Price

Reputation: 11

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;

Upvotes: 1

JonV
JonV

Reputation: 523

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

Upvotes: 2

gbn
gbn

Reputation: 432561

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

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.

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.

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:

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

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

Upvotes: 1

Andomar
Andomar

Reputation: 238256

Restore a backup?

Upvotes: 1

Related Questions