All DBA’s will be tasked at time to refresh a copy of production to a development, reporting or promote as adatbase to a UAT or QA environment. Many times it is important to retain the existing users rights on the target instance.
The script uses the sys.database_role_members, sys.database_principals, sys.database_permissions, sys.sysobjects and sys.sysusers tables.
Some things to note:
- Script does not remove or reduce rights if the user already exists.
- Create user statements that test if users already exits.
- Add users to existing database roles.
- Scripts object level grants.