Copying Logins and users - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Copying Logins and users

Author

Topic

eversm
Starting Member

8 Posts

Posted - 2003-04-09 : 18:45:44

I have multiple instances of SQL and when I refresh databases (Restore from production to lower environments), I loose my user information. Is there a way to copy it using DTS. I can't seem to find the right process to work for me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-09 : 19:02:10

You can use the Transfer Logins Task (only available in SQL 2k) in DTS to do it, but I don't recommend it because it seldom works for me and never gives enough information as to why it failed. I use a stored procedure that I wrote to do it. It uses a cursor to do the work, but oh well. It also uses a linked server, so you'll need to create a linked server for it to work. The linked server would be created on the source server and it would be pointed to the destination server. After you transfer the logins and passwords, just run the sp_change_users_login to unorphan the accounts.

CREATE PROCEDURE isp_Transfer_Logins
AS

SET NOCOUNT ON

DECLARE @login sysname
DECLARE @pwd sysname
DECLARE @new_pwd varchar(255)

DECLARE cur_Users CURSOR FOR
SELECT l.name, l.password
FROM master.dbo.syslogins l
INNER JOIN DBName.dbo.sysusers u ON l.sid = u.sid
WHERE (l.isntname = 0) AND (u.islogin = 1 AND u.isaliased = 0 AND u.hasdbaccess = 1)
ORDER BY u.name

OPEN cur_Users

FETCH cur_Users INTO @login, @pwd

WHILE @@FETCH_STATUS = 0
BEGIN
-- If the login does not exist on the destination server, then add it.
IF ((SELECT count(*) FROM Server1.master.dbo.syslogins WHERE name = @login) = 0)
BEGIN
EXEC Server1.master.dbo.sp_addlogin @loginame = @login, @passwd = @pwd, @encryptopt = skip_encryption, @defdb = 'DBName'
END

-- If the login does exist on the destination server, then synchronize the password.
ELSE
BEGIN
EXEC Server1.master.dbo.sp_droplogin @login
EXEC Server1.master.dbo.sp_addlogin @loginame = @login, @passwd = @pwd, @encryptopt = skip_encryption, @defdb = 'DBName'
END

FETCH cur_Users INTO @login, @pwd
END

CLOSE cur_Users
DEALLOCATE cur_Users

RETURN

Tara

Edited by - tduggan on 04/09/2003 19:07:45

ulyss
Starting Member

10 Posts

Posted - 2003-04-10 : 08:40:53

I got this from somewhere I don't know but it sure works.

-- Setup a linked server called impserver from which the
-- standard logins needs to be transferred. You can call it
-- whatever you want & modify the linked server name also.

declare @login sysname , @password sysname
declare implogins cursor for
select name , password
from /*srv_name.master.dbo.*/syslogins
where isntname != 0
or isntname = 0
and charindex( 'repl_' , name ) = 0
and charindex( 'distributor' , name ) = 0
and name != 'sa'
open implogins
while ( 'FETCH IS OK' = 'FETCH IS OK' )
begin
fetch implogins into @login , @password
if @@fetch_status < 0 break
exec sp_addlogin @login , @password , @encryptopt = 'skip_encryption'
end
deallocate implogins
go

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-10 : 09:37:02

quote:

while ( 'FETCH IS OK' = 'FETCH IS OK' )

How does that work? I would imagine it's still running!

I would go with Taras suggestion.

Tara, why don't you execute sp_change_users_login with in you sproc?

PS your while should be preceeded by a fetch, then:

WHILE @@FETCH_STATUS = 0

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-10 : 12:26:12

I don't execute sp_change_users_login within it because the databases aren't available to do it. They are being log shipped until the point in which we need to switch servers. So we run the transfer logins stored procedure so that the accounts and passwords are over there in case of a failure.

There is a fetch right before the WHILE, so I don't understand what you are saying.

Here is part of the code:
FETCH cur_Users INTO @login, @pwd

WHILE @@FETCH_STATUS = 0

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-10 : 12:45:12

Brett - he's got a BREAK statement in there to exit the loop when he's ready. he just chose a different way of doing a loop while the @@fetchstatus=0.

many ways to skin a cat, I always say! (isn't that a weird expression?)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources