users on stand-by server ? - 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)

users on stand-by server ?

Author

Topic

gercr
Yak Posting Veteran

53 Posts

Posted - 2004-06-23 : 10:47:10

Hi all, I inmplement a Loging ship schema and works fine (thanks tara for the help), well so a big question, how I do to keep the users update on my stand-by server ??, I can't do loging ship of master, and to restore master from my overnigth backup is a task too manual. Any ideas??

Thanks for the help.

Gerardo G.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-23 : 14:35:18

I use isp_Transfer_Logins which is a stored procedure that I wrote to transfer logins from one server to the next. It uses linked servers. Search the forums for it. I've posted it a few times. If a user already exists, it drops and recreates it so that we get the updated password (if it was update that is). If it doesn't exist, it creates it.

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-06-23 : 15:26:42

You could modify the following code to work (only if you are using SQL authentication). I think this came from sqlmag. This keeps the same GUID and password on both hosts. Pretty useful really.

The output is a little ugly, but it works.

SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0

-ec

gercr
Yak Posting Veteran

53 Posts

Posted - 2004-06-23 : 17:13:47

Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources