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

Question

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?

Answer 1

Restore a backup?

Answer 2

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?