How to Find Redundant Granted Permissions on Users and Roles

Question

Is there a way to find when a permission was last or ever used? I am using 2008 R2 Enterprise.

I have logins and database users that have been granted permissions either directly or through roles and over time some have almost certainly become redundant. I'd like to discover which permissions are being used so that I can revoke those that have never been used or last used a long time ago.

TIA

Charles

Answer 1

No, there is not a way that I can think of to find when a permission was last used.  Also, if a permission is specified more that once via different paths (one to a user, one to a group or role that contains the user, etc) you will have to decompose all the rights being granted.

(Note: Using SQL Server 2008 Enterprise Edition or later there is an auditing function that can be used to see who has done what, including what SELECTs that they have run.   Over time you can build up an inferred picture of the rights granted, but no guarantees of finding the duplicates.)

What you can do is determine which rights are granted to each user (group or login) and role in a database or at the server level.   You can also see who is a member of a role.  (You will need to get the Windows Group memberships from the Active Directory for Windows Logins.) Then you can analyse the overlaps in rights and compare that to the common members of each group.

To analyze this sort of thing, I have used Excel's sorting and grouping features to try to get a handle on the data once I have selected it from the SQL Server.

My biggest success with this type of issue is to target a particular area of the security.  Work through the rights that we want  to grant and create new security roles.  Put users in the new roles, announce the deletion of the old rights, wait a bit, drop the rights, and respond to problems. 

FWIW,
RLF

Answer 2

Hi Russell

Thanks for the reply. Disappointing to hear that there isn't a way to do this as I can imagine many would find it useful. I will see about trying some of your suggestions.

Cheers

Charles