Database privileges by table Report

I would like to write a query on a sql 2008r2 that will report all the users that have delete, insert, update and select access by table in a database, either directly by windows or SQL Login or due to roles, etc.

This report would be used for security auditing purposes.  I need to provide a report for each of our production databases to our auditors.  Not sure if anyone has a query that will fit my needs completely, but hopefully something that will give me a good start.
IllinoisDaveAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
For AD group members, you'll need a separate table that tells you which users are in which group(s).

When I have more time, I'll put together at least some initial code.
0
 
HuaMin ChenSystem AnalystCommented:
Try
SELECT d.name, l.name
FROM     all_logins l
CROSS    JOIN sys.databases d

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.