the script to extract the complete database permission along with the database role and application role.h

(Before refres)

NOTE: the application role(if present) will be created with the different password. Please change it after refreshing the database.

 

Before refershing the database from the production server to development server we need to take a backup 

the database roles , application roles , user permissions , which role has assigned to which user.

This is necessary because some user may not have the permission to production to update, insert or delete.

but he might have that permission on the same database on the development .

 

when the refreshing is done from prod to dev environment , the production user permissions will get

reflected on the development too. it will not restore the dev user permission which was given to them 

previously before refreshing the database.

 

I have taken into consideration of all the senarieos like object level, role level, and other 26 kind of object

that might be present on the database including the schema owner.


once you get the output from the above query you can save it in the note pad or do Select * into new table 

to keep it in the temp database, what ever makes you comfortable.

 

 Some you might get error like the object not found in the database, like sys.repel commands. This is 

 because the are given at the server level in the master database, you can ignore it.

  and ALSO some time schema error , you can remove the last insert for schema