1

I would like to be able to see a report for every database user and see what specific access rights he has to each db object. I'm a security noob and part of the problem is not having access to even see what users have been created in production.

As I understand it, rights can be set at the db obejct level and these rights may be overridden by membership to a access role or taken away by membership to a deny role.

Does anyone know of a utility that would produce a report of all tables, SPs, view, etc, and for each, list which Users have access to what so I can easily determine eg, "who has SELECT access to table A?" Or, thinking less grand, is there a query that you have handy that could answer that question for a target object name provided that the utility is run by someone with Admin access?

Chad
  • 23,658
  • 51
  • 191
  • 321

1 Answers1

0

This query lists the user permissions in the database:

SELECT 
    (SELECT name FROM sysusers WHERE uid=tblPermissions.grantee_principal_id) as UserName,
    permission_name,
    state_desc,
    (SELECT name FROM sysobjects WHERE id=tblPermissions.major_id)
FROM sys.database_permissions AS tblPermissions WHERE major_id >=0
NTom
  • 11
  • 1
  • 2