x

Scripting Users

I need to add a new user to the server with the same profile, permissions and roles as another existing user. Is there a script that allows for me to copy / replace one users's id with another?

I scripted out the existing user and discovered they have access to 25+ db's and multiple roles on many of them.

so how do i script the new user into those roles?

more ▼

asked Jan 13, 2011 at 08:53 AM in Default

avatar image

siera_gld
1k 82 88 93

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

1 answer: sort voted first

If you have the scripts for the existing user, can you not just find+replace with the new user, and then run the edited scripts?

more ▼

answered Jan 13, 2011 at 09:01 AM

avatar image

Kev Riley ♦♦
65.9k 48 63 81

the script i used to find the existing users databases is not the same as scripting a user to be added to the db

Jan 13, 2011 at 09:10 AM siera_gld

Ah when you said you had 'scripted out' the user I thought you meant you had (for example) right-clicked on the objects in SSMS and chosen 'script...'.

So why not try that: you know each role to edit, so open one in SSMS and go through adding the new user using the UI, then before you commit the change,, click the 'Script' option in the top bar. This will give you the necessary T-SQL script for adding the user to the role - copy and paste it enough times for all the roles, change the rolenames and execute.

Jan 13, 2011 at 09:20 AM Kev Riley ♦♦

Alternatively there are commercial products out there that will do this for you - for example Idera's User Clone http://www.idera.com/Product-Tour/SQL-admin-toolset/SQL-User-Clone/

Jan 13, 2011 at 09:23 AM Kev Riley ♦♦

The roles are different for each db....

so if there is a way to query and return all the scripts for user A then I want to just copy paste to User B...

Jan 13, 2011 at 09:28 AM siera_gld

Kev - The tool is a great trick to have in your sleeves...It took about an hour to generate the script but it is exactly what I'm looking for.

Jan 13, 2011 at 12:42 PM siera_gld
(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x122
x94

asked: Jan 13, 2011 at 08:53 AM

Seen: 1033 times

Last Updated: Jan 13, 2011 at 08:53 AM

Copyright 2017 Redgate Software. Privacy Policy