0

I am looking for a script that I can use to script out all database level permissions.

  • Script users \ permissions
  • Restore DB From Prod to Non Prod
  • Drop Prod Users and Run script to apply original permissions

There are a lot of scripts out there. But i am unsure which one to use. Would be great if someone can point me in the right direction.

Improve this question
4
  • I don't know if you are going to find something tailor made for your requirements, but it should be straightforward to modify something like sp_helprevlogin to suit your purposes.
    – Jonathan Fite
    Commented Mar 20, 2017 at 2:17
  • sp_helprevlogin would be for server level permissions ??
    – Halt_And_Catch_Fire
    Commented Mar 20, 2017 at 2:37
  • 1
    dbatools module - Export-SqlUser
    – user507
    Commented Mar 20, 2017 at 3:01
  • You can try SSMS inbuilt Generate Script Wizard. You have option to generate script for permission,roles and almost every database scoped object
    – Shanky
    Commented Mar 20, 2017 at 4:33

1 Answer 1

Reset to default
1

You should obviously try using native SSMS functionality. To scripts database users and their permissions use 'Generate script' context menu option of the database (right click db - tasks - generate scripts). to restore database from Production to non-Production, again, use Script the action task and then modify it to suit your specific needs (right-click 'Databases' node - restore database - Script button on the upleft of the General tab). enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.