Posted By

edwinet on 03/08/13


Tagged

sql query security sql2000


Versions (?)

Auditing SQL Server User and Role Permissions for Databases


 / Published in: SQL
 

URL: http://www.mssqltips.com/sqlservertip/2132/auditing-sql-server-user-and-role-permissions-for-databases/

The sysprotects system table reports all of the permissions granted or denied in a given database. We'll need to join it with sysusers and sysobjects to get all the information we need. Here's an example query that only pulls information on objects (no CREATE TABLE permissions or anything else at the database level)

  1. SELECT
  2. su.[name] AS 'User',
  3. CASE sp.[protecttype]
  4. WHEN 204 THEN 'GRANT w/ GRANT'
  5. WHEN 205 THEN 'GRANT'
  6. WHEN 206 THEN 'DENY' END AS 'Permission',
  7. CASE sp.[action]
  8. WHEN 26 THEN 'REFERENCES'
  9. WHEN 193 THEN 'SELECT'
  10. WHEN 195 THEN 'INSERT'
  11. WHEN 196 THEN 'DELETE'
  12. WHEN 197 THEN 'UPDATE'
  13. WHEN 224 THEN 'EXECUTE' END AS 'Action',
  14. so.[name] AS 'Object'
  15. FROM sysprotects AS sp
  16. INNER JOIN sysusers AS su
  17. ON sp.[uid] = su.[uid]
  18. INNER JOIN sysobjects AS so
  19. ON sp.[id] = so.[id]
  20. WHERE sp.[action] IN (26, 193, 195, 196, 197, 224)
  21. ORDER BY su.[name], so.[name];

Report this snippet  

You need to login to post a comment.