Reverse-Engineer Database Permissions (2005 up)

  • Comments posted to this topic are about the item Reverse-Engineer Database Permissions (2005 up)

    ---------------------------------------------------------------------

  • Hello

    Interesting script but it seems some grant are not included.

    I have a database with a role "dev" which I granted

    GRANT CREATE PROCEDURE,CREATE FUNCTION,CREATE VIEW TO [DEV_ROLE];

    GRANT ALTER ON SCHEMA::[THIS_SCHEMA1] TO [DEV_ROLE];

    GRANT SHOWPLAN TO [DEV_ROLE] ;

    but only ALTER was restore by your stored procedure

    I haven't check why for now

  • I added that to find Database permissions

    --DATABASE permissions

    SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name + 'TO [' + USER_NAME(grantee_principal_id)+']' +

    case

    when state_desc like '%with_grant_option' then ' with grant option'

    else

    ' '

    end

    as '-- Database permissions'

    FROM sys.database_permissions (NOLOCK)

    WHERE class_desc = 'DATABASE' and type !='CO'

    ORDER BY USER_NAME(grantee_principal_id),DB_NAME(major_id)

  • thank you pmonschein, thats an improvement.

    I have been too restrictive in my last select statement in specifying view definition, so just replace that select with your statement. As the proc is within a database I wouldn't include the DB_NAME(major_id) in the order by clause.

    also put a space before 'TO' so there is a space in the reverse engineered code, i.e

    --DATABASE permissions

    SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name + ' TO [' + USER_NAME(grantee_principal_id)+']' +

    case

    when state_desc like '%with_grant_option' then ' with grant option'

    else

    ' '

    end

    as '-- Database permissions'

    FROM sys.database_permissions (NOLOCK)

    WHERE class_desc = 'DATABASE' and type !='CO'

    ORDER BY USER_NAME(grantee_principal_id)

    ---------------------------------------------------------------------

  • Thanks for this!

    The author mentions: "There are a number of scripts out there to do this for SQL2000."

    Could someone post a link to one of those scripts for SQL 2000?

    Thanks for any help,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • A google search of 'script user permissions sql server 2000' returns a number but attached is one we used to use.

    edit - if you found the script useful, please vote on it!

    ---------------------------------------------------------------------

  • Thanks for the script.

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

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