Database users and user permission transfer into different database.
Hi, I need to perform given below two task on same instance but want to avoid long and manual process to perform this for single-2 user. 1. Drop all existing database users from db_2? 2. Transfer all users and related user permission from db_1 to db_2. 3. User permission transfer process should be include related database role as well indivisibly object permission. Drop Users and Create users script could be generate through generate script option available on SSMS but I did not get any generate option for associated permission with users. please suggest some process or script to perform task fast. Thanks
You can take inspiration on cloning users rights in my post: [Cloning user rights in database]. In this post is presented a system stored procedure to handle cloning, but you can easily update the solution to script rights for all users and roles. Eventually you can call it in a loop for each appropriate user and/or role from the `sys.database_principals` system table. :