Log shipping & users | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Log shipping & users

I’ve setup log shipping from scratch and told it to create the destination database. I’ve set the Secondary Load state to Standyby Mode and the status stays everything is fine and in sync, however when I check to see if the users have been created they have not. What has happened? Thanks

As far as i know the login’s don’t get carried over automatically. you have to set up a DTS package to transfer logins and a cpl of jobs to bcp them accross. See "How to set up and perform a log shipping role change (Transact-SQL)" in BOL for more details
Check the monitoring server for log shipping working and as suggested by Hak. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I use 2 stored procedured to sync up my logins. The first is below. It pulls the logins from my primary server(sql1) and loads them to my secondary server. CREATE PROCEDURE usp_Syncroize_Logins_from_prod AS
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,
FROM sql1.master.dbo.syslogins AS l
WHERE l.loginname IS NOT NULL
OPEN @logins WHILE(1=1)
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’
EXEC sp_grantlogin @name
EXEC sp_defaultdb @name, @dbname
EXEC sp_defaultlanguage @name, @language
Here is my second stored procedure. It fixes any orphan logins on the secondary server.
CREATE proc usp_fix_orphan_users
set nocount on
declare @name sysname
declare @username sysname
declare @SQL nvarchar(600) declare c1 cursor for
select name from master.dbo.sysdatabases open c1
fetch c1 into @name
while @@fetch_status >= 0
select @SQL = ‘DECLARE orphan_users cursor for
select name
from ‘ + @name + ‘.dbo.sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null’
OPEN orphan_users
fetch orphan_users into @username
while @@fetch_status >= 0
select @SQL = ‘Use ‘ + @name +
‘ EXEC sp_change_users_login "update_one", ‘ +
@username + ‘, ‘ + @username
fetch orphan_users into @username
deallocate orphan_users fetch c1 into @name
deallocate c1