Louis Davidson gives us a procedure which provides effective rights for a principal:
In my blog Calculating a Security Principal’s Effective Rights. I built a view, named Utilty.EffectiveSecurity that you could query to fetch a security principal’s rights to objects in a database. In that blog I tested the code and showed how it works. Now I have taken this to the extreme and expanded the view to include all of the user’s security by finding all of their rights to all of the things that the get rights for.
The list of possible permissions you can fetch can be retrieved from:
This returns the following 26 types of things that can have permissions assigned and returned by the sys.fn_my_permissions function:
Read on for the code.