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
Moderator
http://www.SQL-Server-Performance.Com/forum
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,
l.isntuser
FROM sql1.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
Here is my second stored procedure. It fixes any orphan logins on the secondary server.
CREATE proc usp_fix_orphan_users
as
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
begin
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’
EXECUTE (@SQL)
OPEN orphan_users
fetch orphan_users into @username
while @@fetch_status >= 0
begin
select @SQL = ‘Use ‘ + @name +
‘ EXEC sp_change_users_login "update_one", ‘ +
@username + ‘, ‘ + @username
EXECUTE (@SQL)
fetch orphan_users into @username
end
deallocate orphan_users fetch c1 into @name
end
deallocate c1

]]>