Post reply

Reverse-Engineer Database Permissions (2005 up)

  • October 22, 2011 at 4:01 pm

    #244943

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

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

  • October 24, 2011 at 7:07 am

    #1398654

    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

  • October 24, 2011 at 7:26 am

    #1398665

    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)

  • October 24, 2011 at 9:38 am

    #1398763

    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)

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

  • December 4, 2012 at 10:05 am

    #1565415

    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

  • December 4, 2012 at 11:35 am

    #1565454

    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!

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

  • May 12, 2016 at 6:58 am

    #1878543

    Thanks for the script.

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

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