Post reply

Finding 'special' access for user and user defined roles

  • February 18, 2010 at 12:44 pm

    #216846

    [font="Comic Sans MS"]

    Dear All,

    I am working on an audit requirement across an enterprise system – this consist of SQL Server versions 2000/2005/2008. The general practice is to grant access to user through fixed database role. However - there are some deviations like:

    1)A user-defined role is created on one of the database and specific grant has been given to selected objects. Example would be execute permission on 5 stored procedures + select permission on 10 tables + delete permission on 3 tables.

    2)A user has been granted direct access to few objects (like above) but not through a user defined role.

    Is there a way to list down the details of (1) and (2)

    That is:

    (1) User Defined Rolename -> Object Name (s) -> what permission on the object

    (2) Userid -> Object Name (s) -> what permission on the object

    The idea is to filter the user who has some ‘special’ access and review the user defined roles.

    Any help on this would be highly appreciated.

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • February 19, 2010 at 8:10 am

    #1121862

    [font="Comic Sans MS"]

    Just in case someone needs this - it a simple join but took a while for me to find the tables. Also query this uses old 2000 system views and not sys. tables to cover all types of servers.

    select sp.uid,su.name,so.name, accessValue =

    case

    when sp.action = 026 then 'REFERENCES'

    when sp.action = 178 then 'CREATE FUNCTION'

    when sp.action = 193 then 'SELECT'

    when sp.action = 195 then 'INSERT'

    when sp.action = 196 then 'DELETE'

    when sp.action = 197 then 'UPDATE'

    when sp.action = 198 then 'CREATE TABLE'

    when sp.action = 203 then 'CREATE DATABASE'

    when sp.action = 207 then 'CREATE VIEW'

    when sp.action = 222 then 'CREATE PROCEDURE'

    when sp.action = 224 then 'EXECUTE'

    when sp.action = 228 then 'BACKUP DATABASE'

    when sp.action = 233 then 'CREATE DEFAULT'

    when sp.action = 235 then 'BACKUP LOG'

    when sp.action = 236 then 'CREATE RULE'

    end

    , accessvalueType = case

    when sp.protecttype = 204 then 'GRANT_W_GRANT'

    when sp.protecttype = 205 then 'GRANT'

    when sp.protecttype = 206 then 'DENY'

    end,'IsAppRole' = isapprole,

    'IsSqlrole' = issqlrole

    from sysobjects so

    inner join sysprotects sp

    on so.id = sp.id

    inner join sysusers su

    on su.uid = sp.uid

    --and (issqlrole = 1 or isapprole = 1)

    and su.name != 'public'

    order by 1

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply