SQL Server Performance

Log shipping & users

Discussion in 'SQL Server Log Shipping' started by fenster, Nov 23, 2004.

  1. fenster New Member

    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
  2. Hak New Member

    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
  3. satya Moderator

    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.
  4. jgavern New Member

    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


Share This Page