• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

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.
0
IllinoisDave
Asked:
IllinoisDave
1 Solution
 
HuaMinChenBusiness AnalystCommented:
Try
SELECT d.name, l.name
FROM     all_logins l
CROSS    JOIN sys.databases d

Open in new window

0
 
Scott PletcherSenior 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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now