Results 1 to 9 of 9

Thread: Is there any tool that documents all the users and their privilage levels?

  1. #1
    Join Date
    Aug 2004
    Location
    USA
    Posts
    80

    Is there any tool that documents all the users and their privilage levels?

    Is there any tool that documents all the users and their privilage levels in a given database??

    I looked at SQL Scribe tool but it doesn't care about documenting users at all!

    We need this for documenting hundreds of databases for SOX purpose.
    Thanks,

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Try with sp_helplogins, it lists all db roles the login belongs to.

  3. #3
    Join Date
    Aug 2004
    Location
    USA
    Posts
    80
    sp_helplogins can't serve my requirement as it doesn't provide detail info as to what privilages are defined within a role if role based security is assigned. It will just list the name of the role under the user's login.

    I need to see detail info in a report format without having to query multiple times for each login and role separately.

    Any third-party tool available for this??

  4. #4
    Join Date
    Feb 2003
    Location
    Aussie Land
    Posts
    114
    This script goes down to object level returning all Users and Roles in the NorthWind database.

    -- Get User/Roles
    use NorthWind

    declare @chvDBName varchar(50)
    set @chvDBName = 'NorthWind'

    SELECT
    'Database' = @chvDBName,
    'UserOrRoleName' = sysusers.name,
    Permission = CASE action
    WHEN 193 THEN 'SELECT'
    WHEN 195 THEN 'INSERT'
    WHEN 196 THEN 'DELETE'
    WHEN 197 THEN 'UPDATE'
    WHEN 26 THEN 'REFERENCE'
    WHEN 224 THEN 'EXECUTE'
    ELSE 'Unknown'
    END
    , 'ObjectName' = sysobjects.name
    , 'ObjectType' = case when sysobjects.xtype = 'U' then 'Table'
    when sysobjects.xtype = 'V' then 'View'
    when sysobjects.xtype = 'P' then 'Procedure'
    else 'Other'
    end,
    'Is_Role' = case when issqlrole = 1 then 'Yes' else 'No' end
    FROM sysprotects, sysobjects, sysusers
    WHERE sysobjects.id = sysprotects.id
    AND sysprotects.action IN (193, 195, 196, 197, 224, 26)
    AND sysprotects.uid = sysusers.uid
    AND sysobjects.name not like 'dt[_]%'
    AND sysobjects.name not like 'dt%'
    AND sysobjects.name not like 'sel[_]%'
    AND sysobjects.name not like 'sp_ins[_]%'
    AND sysobjects.name not like 'sp_upd[_]%'
    AND sysobjects.name not like 'sp_sel[_]%'
    AND sysobjects.name not like 'sp_cft[_]%'
    AND sysobjects.name not like 'ctsv[_]%'
    AND sysobjects.name not like 'tsvw[_]%'
    AND sysusers.name not like 'MSmerge[_]%'
    AND sysobjects.xtype <> 'S'
    AND sysobjects.name not in ('MSsubscription_agents', 'sysmergearticles', 'sysconstraints', 'syssegments')
    ORDER BY sysusers.name, sysobjects.xtype, sysobjects.name
    GO


    FOR LOGINS:
    --------------------------------------
    select
    'Server' = UPPER(@@SERVERNAME),
    'LoginName' = loginname,
    'HasAccess' = case when hasaccess = 1 then 'Yes' else 'No' end,
    'LoginType' = case when isNTname = 1 then 'NTUserOrGroupLogin' else 'SQLServerLogin' end,
    'Is_NT_Group' = case when isNTgroup = 1 then 'Yes' else 'No' end,
    'Is_NT_User' = case when isNTuser = 1 then 'Yes' else 'No' end,
    'SrvRole_sysadmin' = case when sysadmin = 1 then 'Yes' else 'No' end,
    'SrvRole_securityadmin' = case when securityadmin = 1 then 'Yes' else 'No' end,
    'SrvRole_serveradmin' = case when serveradmin = 1 then 'Yes' else 'No' end,
    'SrvRole_setupadmin' = case when setupadmin = 1 then 'Yes' else 'No' end,
    'SrvRole_processadmin' = case when processadmin = 1 then 'Yes' else 'No' end,
    'SrvRole_diskadmin' = case when diskadmin = 1 then 'Yes' else 'No' end,
    'SrvRole_dbcreator' = case when dbcreator = 1 then 'Yes' else 'No' end
    from master..syslogins




    As for sarbanes....... I dont think i want to see another catastrophe like u know who.

  5. #5
    Join Date
    Aug 2004
    Location
    USA
    Posts
    80
    Excellent!! thanks KingSexy182.

  6. #6
    Join Date
    Aug 2004
    Location
    USA
    Posts
    80
    I need to include DENY in your code, how do I find what is the corresponding number for DENY?
    ----------
    SELECT
    'Database' = @chvDBName,
    'UserOrRoleName' = sysusers.name,
    Permission = CASE action
    WHEN 193 THEN 'SELECT'
    WHEN 195 THEN 'INSERT'
    WHEN 196 THEN 'DELETE'
    WHEN 197 THEN 'UPDATE'
    WHEN 26 THEN 'REFERENCE'
    WHEN 224 THEN 'EXECUTE'
    ELSE 'Unknown'
    END
    -----------

  7. #7
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    declare @chvDBName varchar(50)
    set @chvDBName = db_name()

    SELECT
    'Database' = @chvDBName,
    'UserOrRoleName' = sysusers.name,
    'GrantType' = case protecttype
    when 204 then 'RANT_W_GRANT'
    when 205 then 'GRANT'
    when 206 then 'REVOKE' end,
    Permission = CASE action
    WHEN 193 THEN 'SELECT'
    WHEN 195 THEN 'INSERT'
    WHEN 196 THEN 'DELETE'
    WHEN 197 THEN 'UPDATE'
    WHEN 26 THEN 'REFERENCE'
    WHEN 224 THEN 'EXECUTE'
    ELSE 'Unknown'
    END
    , 'ObjectName' = sysobjects.name
    , 'ObjectType' = case when sysobjects.xtype = 'U' then 'Table'
    when sysobjects.xtype = 'V' then 'View'
    when sysobjects.xtype = 'P' then 'Procedure'
    else 'Other'
    end,
    'Is_Role' = case when issqlrole = 1 then 'Yes' else 'No' end
    FROM sysprotects, sysobjects, sysusers
    WHERE sysobjects.id = sysprotects.id
    AND sysprotects.action IN (193, 195, 196, 197, 224, 26)
    AND sysprotects.uid = sysusers.uid
    AND sysobjects.name not like 'dt[_]%'
    AND sysobjects.name not like 'dt%'
    AND sysobjects.name not like 'sel[_]%'
    AND sysobjects.name not like 'sp_ins[_]%'
    AND sysobjects.name not like 'sp_upd[_]%'
    AND sysobjects.name not like 'sp_sel[_]%'
    AND sysobjects.name not like 'sp_cft[_]%'
    AND sysobjects.name not like 'ctsv[_]%'
    AND sysobjects.name not like 'tsvw[_]%'
    AND sysusers.name not like 'MSmerge[_]%'
    AND sysobjects.xtype <> 'S'
    AND sysobjects.name not in ('MSsubscription_agents', 'sysmergearticles', 'sysconstraints', 'syssegments')
    ORDER BY sysusers.name, sysobjects.xtype, sysobjects.name
    GO

  8. #8
    Join Date
    Aug 2004
    Location
    USA
    Posts
    80
    Thank you MAK. That is working great!!

  9. #9
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Thanks to King.

    I just updated his script.

Posting Permissions

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