Database users and user permission transfer into different database.


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.


more ▼

asked Apr 16, 2012 at 04:52 PM in Default

avatar image

1.4k 90 93 97

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Apr 16, 2012 at 06:16 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Do you have any script for generate SQL script for create database role along with related permissions.

Apr 17, 2012 at 09:06 AM Sharma
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 16, 2012 at 04:52 PM

Seen: 1226 times

Last Updated: Apr 17, 2012 at 09:06 AM

Copyright 2018 Redgate Software. Privacy Policy