how to transfer specific db users and passwords to another db??

Question

Post reply

how to transfer specific db users and passwords to another db??

s1123

Mr or Mrs. 500

Points: 567
More actions
April 19, 2012 at 3:41 am

#267732

hi guys,

i have this one db(principal db) in first server and i want to transfer the users/logins and passwords to another db(miroor db) in second server,

can anyone tel me which is the best process.

i know about transfering all logins in 1 server to another sever but all i want is specific db users and logins with passwords..

thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Diyas SSCarpal Tunnel Points: 4845 More actions · Answer 1

Diyas

SSCarpal Tunnel

Points: 4845

April 19, 2012 at 4:32 am

#1475924

all logins and password are saved in master database.

you need to script out of users from master database and excuete in your target server

and fix the logins /Users

Ant-Green SSC Guru Points: 112965 More actions · Answer 2

Ant-Green

SSC Guru

Points: 112965

April 19, 2012 at 4:41 am

#1475931

try this link,also look at transfer login task in SSIS, manually scripting from the master database wrapped in with the create login commands you need.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Use the SSIS transfer login task or use a script that copies the SIDs over as well as the login names. With either of those you won't have orphaned login problems on the second server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

s1123 Mr or Mrs. 500 Points: 567 More actions · Answer 4

s1123

Mr or Mrs. 500

Points: 567

April 19, 2012 at 5:39 am

#1475948

thnx for the replies, got some info on using ssis....

but will it transfer the users without changing ssid's?

thanks

Perry Whittle SSC Guru Points: 233792 More actions · Answer 5

s1123 (4/19/2012)

hi guys,

i have this one db(principal db) in first server and i want to transfer the users/logins and passwords to another db(miroor db) in second server,

can anyone tel me which is the best process.

i know about transfering all logins in 1 server to another sever but all i want is specific db users and logins with passwords..

thanks

do you have the passwords for the accounts you are transferring?

How many logins are we talking about transferring here?

Your best bet might be to use sp_change_users_login. Check more info on this SP at my blog

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Perry Whittle (4/19/2012)

Your best bet might be to use sp_change_users_login.

Except that he'd have to fail the mirroring over to run that, and that could affect the users on the formerly principal database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 7

the transfer login task disables the login and assigns a random password on the destination, so unless you know all the passwords its pretty useless.

You can tell it to keep the sids

I'd go with sp_help_revlogin, that keeps sids and passwords. you would have to tweak the code to only extract the ids you were interested in or simply only pipe in the ones you wanted.

---------------------------------------------------------------------

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 8

USE database_name



DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT  @sql = @sql + 'EXEC master.dbo.sp_help_revlogin @login_name = N''' + sp.name + ''';

'

FROM    sys.database_principals dp

        JOIN sys.server_principals sp ON dp.sid = sp.sid;

PRINT @sql;

--EXEC(@sql);

Run the above SQL against your primary database, then execute the results to get the login info to take to the secondary. Use the link Anthony.Green provided to get a copy of sp_help_revlogin (and supporting hex proc) to add to master (or wherever).

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

s1123 Mr or Mrs. 500 Points: 567 More actions · Answer 9

s1123

Mr or Mrs. 500

Points: 567

April 19, 2012 at 9:50 pm

#1476396

thank u all for replies

i will try and let u guys know..

thnaks