x

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
more ▼

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

Amardeep gravatar image

Amardeep
1.3k 86 88 89

(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][1].

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.

[1]: http://www.pawlowski.cz/2011/03/cloning-user-rights-database/
more ▼

answered Apr 16, 2012 at 06:16 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Do you have any script for generate SQL script for create database role along with related permissions.
Apr 17, 2012 at 09:06 AM Amardeep
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x711
x19
x10
x1

asked: Apr 16, 2012 at 04:52 PM

Seen: 989 times

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