September 28, 2007 at 5:09 pm
Hi,
I have restored my databases restore from one SQL server 2000 to another SQL 2000 Server. Then, I use DTS Transfer Logins untilities to transfer logins. I can see that this DTS runs successfully. In addition, I could check that the all users are transfered. But when I chen these users's permissions, it is lost. So I am wondering should I transfer Logins first by DTS Tools or restore those Databases first, then transfer server Lonins.
Many thanks.
Don:)
October 1, 2007 at 3:12 am
THis is what i did wehn restoring/transferring databases in different server
1. Run List Log-in and Users (version2000) script
- (I think I got this in the script section of sqlservercentral)
- See attachment below (text file)
2. After running the script you can now detect the server and database user and log-ins.
3. Create a SQL Script for Server Log-ins and DB User Log-ins
For Server Log-ins:
CREATE TABLE login
(
names varchar(124),
pass varchar(124),
db varchar(124),
)
INSERT INTO login VALUES ('server log-in user','password','database')
DECLARE @login varchar(124)
DECLARE @q varchar(1024)
DECLARE @w varchar(1024)
DECLARE logins CURSOR FOR
select ''''+names+''''+', '+''''+pass+''''+', '+''''+db+'''' AS 'LOG' FROM login
OPEN logins
FETCH NEXT FROM logins INTO @login
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @q = 'sp_addlogin '+@login
EXEC (@q)
FETCH NEXT FROM logins INTO @login
END
CLOSE logins
DEALLOCATE logins
DROP TABLE login
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--Add Role to the Log-in
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
DECLARE @login2 varchar(1024)
CREATE TABLE login2
(
names varchar(1024),
role varchar(3000)
)
INSERT INTO login2 VALUES ('server log-in','sysadmin')--there are others like bulkadmin etc.
DECLARE logins CURSOR FOR
select names+','+role FROM login2
OPEN logins
FETCH NEXT FROM logins INTO @login2
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @w = 'sp_addsrvrolemember '+@login2
EXEC (@w)
FETCH NEXT FROM logins INTO @login2
END
CLOSE logins
DEALLOCATE logins
DROP TABLE login2
For DB Users:
--@@@@@lapauser@@@@@--
DECLARE @lapauser varchar(1024)
DECLARE @lapauser2 varchar(1024)
DECLARE @lapauser3 varchar(1024)
DECLARE lapauser CURSOR for
select name from master..sysdatabases where name = 'dbname'
OPEN lapauser
FETCH NEXT FROM lapauser INTO @lapauser
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @lapauser2 = @lapauser+'..sp_grantdbaccess '+'db user'
SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_owner, db user'
EXEC (@lapauser2)
EXEC (@lapauser3)
SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_accessadmin, db user'
EXEC (@lapauser3)
SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_securityadmin, db user'
EXEC (@lapauser3)
SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_ddladmin, db user'
EXEC (@lapauser3)
SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_backupoperator, db user'
EXEC (@lapauser3)
SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_datareader, db user'
EXEC (@lapauser3)
SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_datawriter, db user'
EXEC (@lapauser3)
SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_denydatareader, db user'
EXEC (@lapauser3)
SET @lapauser3 = @lapauser+'..sp_addrolemember '+'db_denydatawriter, db user'
EXEC (@lapauser3)
FETCH NEXT FROM apauser INTO @lapauser
END
CLOSE lapauser
DEALLOCATE lapauser
-I know this process is very long but once done you will just execute the scripts and your server/db log-in/users are already transferred. Any short methods are highly appreciated
"-=Still Learning=-"
Lester Policarpio
October 3, 2007 at 1:07 pm
You can also use the Transfer logins task in DTS Designer. It's pretty straight forward to use.
Markus
[font="Verdana"]Markus Bohse[/font]
October 3, 2007 at 2:15 pm
I prefer to use the technique described in this Microsoft KB article:
How to transfer logins and passwords between instances of SQL Server[/url]
It'll ensure your SIDs match up, meaning you won't have to run sp_change_users_login within the databases. Also, it provides a method to capture and store the logins for use in a disaster recovery scenario without revealing the passwords directly.
K. Brian Kelley
@kbriankelley
October 3, 2007 at 6:17 pm
Thanks Markus,
I tried DTS but it is shallow copy. Now I solve my problem from a stored procedure called sp_help_revlogin from Microsoft website.
Many Thanks,
Don
October 3, 2007 at 6:21 pm
I tried DTS first but it is shallow copy, which means it only copies user name but lost the permissions associtaed that user. Now I solve my problem from a stored procedure called sp_help_revlogin from the link you mention before.
Many Thanks,
Don
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply