Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Unhappy Unanswered: SQL Secuirty Audit

    Hi Folks,

    Have a scenario where we have to audit all our databases and servers for changes in security accross the servers.

    We have a central montioring server where we pull SQL metadata at regular intervals. In this instance we are looking to have before and after snapshots of the SQL system tables.

    For the logins this is fine as there is a last updated field in the syslogins table. We can tell when a new user has been added , remmoved or the login has changed ie the login as been added to sysadmin fixed server role... etc Perfect !!!

    What Im trying to work out now is how I can do this for object level permsisions. Have looked at sysprotects but no joy. If a user or a role has been granted select or update on a table... How can I tell based on before and after snap shots of the system tables what permissons have changed and whom have they changed for...

    Help ......

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Some time ago, I played around with the sysprotects table, and found this is the key to it:

    action permission
    ----------- --------------------
    26 references
    193 select
    195 insert
    196 delete
    197 update
    198 create table
    207 create view
    222 create procedure
    224 exec procedure
    233 create default
    236 create rule


    You can confirm all of this by creating a new table in say the pubs database, and bounce the permissions around. UID and ID should be relatively obvious. After that, how to monitor changes is up to you.

  3. #3
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Talking

    select
    sysusers.name as [Role or User Name],
    CASE sysusers.issqlrole
    WHEN 1 THEN 'Role' ELSE 'User' END AS [Type],
    sysobjects.name AS [Object Name],
    sysobjects.type AS [Object Type],
    CASE sysprotects.action
    WHEN 193 THEN 'SELECT'
    WHEN 195 THEN 'INSERT'
    WHEN 196 THEN 'DELETE'
    WHEN 197 THEN 'UPDATE'
    WHEN 224 THEN 'EXECUTE PROC'
    WHEN 198 THEN 'CREATE TABLE'
    WHEN 207 THEN 'CREATE VIEW'
    WHEN 222 THEN 'CREATE PROC'
    WHEN 233 THEN 'CREATE DEFAULT'
    WHEN 236 THEN 'CREATE RULE'
    WHEN 26 THEN 'DRI'
    ELSE 'No Permissions set'
    END AS [Permission],
    case sysprotects.protecttype
    when 205 then 'Grant'
    when 206 then 'Deny' end
    from sysobjects join sysprotects on sysobjects.id=sysprotects.id
    INNER JOIN sysusers ON sysprotects.uid = sysusers.uid
    go

    -- Thanks A Bunch

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •