Re: Automate syncing login ids of master db at remote server

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I have continued to search the web for a solution and have found some
scripts provided by Umachandar Jayachandran at
http://www.sqlmag.com/Article/ArticleID/25710/sql_server_25710.html,
which seem to work, in that the logins ids are located within
sysxlogins table within the master db on the standby server, however
these logins are not visible under users within Enterprise Manager,
does this matter?

CREATE PROCEDURE sp_Syncronize_Logins_from_prod AS
set ANSI_NULLS OFF
set ANSI_WARNINGS OFF
DECLARE @logins cursor
DECLARE @name sysname, @password sysname,
@dbname sysname, @language sysname,
@sid binary(16), @isntuser bit
SET @logins = cursor fast_forward FOR
SELECT l.loginname, l.password, l.dbname, l.language, l.sid,
l.isntuser
FROM [server\instance].master.dbo.syslogins AS l
WHERE l.loginname IS NOT NULL
OPEN @logins

WHILE(1=1)
BEGIN
FETCH @logins INTO @name, @password, @dbname,
@language, @sid, @isntuser
IF @@fetch_status < 0 break

IF is_srvrolemember( 'sysadmin', @name ) IS NOT NULL
CONTINUE

IF @isntuser = 0
EXEC sp_addlogin @name, @password, @dbname,
@language, @sid, 'skip_encryption'
ELSE
BEGIN
EXEC sp_grantlogin @name
EXEC sp_defaultdb @name, @dbname
EXEC sp_defaultlanguage @name, @language
END
END

DEALLOCATE @logins

.

Relevant Pages

  • Re: Automate syncing login ids of master db at remote server
    ... This communication is an original work and represents my sole ... these logins are not visible under users within Enterprise Manager, ... DECLARE @name sysname, @password sysname, ... EXEC sp_addlogin @name, @password, @dbname, ...
    (microsoft.public.sqlserver.replication)
  • Re: Admin users on SQL Server
    ... dbname SYSNAME NOT NULL, ... sid VARBINARYNOT NULL, ... DECLARE dbcsr INSENSITIVE CURSOR FOR ... INSERT INTO #logins ...
    (microsoft.public.sqlserver.security)
  • Re: Admin users on SQL Server
    ... > dbname SYSNAME NOT NULL, ... > sid VARBINARYNOT NULL, ... > DECLARE dbcsr INSENSITIVE CURSOR FOR ... > INSERT INTO #logins ...
    (microsoft.public.sqlserver.security)
  • Re: Transferring logins form 6.5 to 2000, anyone?!
    ... In fact it is possible to transfer 6.5 logins to 2000 including the ... server is case-insenstive, and the 2000 server is case-sensitive, then you ... @65Password sysname, -- password on the 6.5 server ... > password from SQL Server 6.5 to SQL Server 2000 is to upgrade the ...
    (microsoft.public.sqlserver.security)
  • RE: Generating a Whos Online list
    ... Status sysname NULL, ... CPUTime int NULL, ... ProgramName sysname NULL, ... insert into #logins exec sp_who2 ...
    (microsoft.public.sqlserver.programming)