none
script to script out exisitng permissions

    Question

  • I am looking for a script to script out exisitng permissions from the database before it is refreshed.Is there an easy way to do this?
    I have been doing this manually for small databases with less users but it will be dificult for huge database with hundreds of users.
    Wednesday, May 16, 2018 7:37 AM

Answers

  • Hi.

    I think you can use database scripts.

    SSMS->login->Server->Database->right click->Tasks->Generate scripts->choose what you want


    And then, go to advanced tab.

    change the below properties from false to true.

    Wednesday, May 16, 2018 7:45 AM
  • I am looking for a script to script out exisitng permissions from the database before it is refreshed.Is there an easy way to do this?
    I have been doing this manually for small databases with less users but it will be dificult for huge database with hundreds of users.

    You can use the script provided by Kenneth below. Just give the DB name as input and it'd script out the "create" and "rollback" scripts for you. The following three things are scripted-out by this script.

    1. DB users creation

    2. Role membership

    3. Object level permissions

    https://sqlstudies.com/free-scripts/sp_dbpermissions/

    Hope this helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by jam56 Friday, June 1, 2018 4:33 AM
    Wednesday, May 16, 2018 9:46 AM

All replies

  • Hi.

    I think you can use database scripts.

    SSMS->login->Server->Database->right click->Tasks->Generate scripts->choose what you want


    And then, go to advanced tab.

    change the below properties from false to true.

    Wednesday, May 16, 2018 7:45 AM
  • I am looking for a script to script out exisitng permissions from the database before it is refreshed.Is there an easy way to do this?
    I have been doing this manually for small databases with less users but it will be dificult for huge database with hundreds of users.

    You can use the script provided by Kenneth below. Just give the DB name as input and it'd script out the "create" and "rollback" scripts for you. The following three things are scripted-out by this script.

    1. DB users creation

    2. Role membership

    3. Object level permissions

    https://sqlstudies.com/free-scripts/sp_dbpermissions/

    Hope this helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by jam56 Friday, June 1, 2018 4:33 AM
    Wednesday, May 16, 2018 9:46 AM
  • Sorry for late reply.Both of your responses worked.
    Friday, June 1, 2018 4:40 AM