Losing user logins when restoring database to new server

  • Yesterday my supervisor showed me how to restore a database from one server (the test server) to another (production) server.

    The problem was that at the end I had to go ahead and manually add all the users (about 12) to the newly restored database on the production server.

    He isnt sure why we lose the information, and I would like to know if ther is a workaround (there must be!)

    I have read a little about some strategies to go ahead and do this (sp_change_users_login), but I wanted to ask you fellas also.

    Thanks for your time.

    DJ

  • longtime known issue.

    logins on a server are stored in the master database. access to the login is stored in the actual database (select permissions only, db_datareader for example) are inside the database you resotred.

    if you restore a database on a DIFFERENT server, the login doesn't exist. It wouldn't be right security wise that if you restored a database, it instantly gave logins access to a different server that were not explicitly assigned.

    How would you know that my user "Bob" on my domain "Sneaky" is the same "Bob" on your domain? you can infer it based on the name, so if you have a "Bob", you can use the stored proc to fix the login.

    I typically delete all users from a restored database form other servers, then assign the logins that do exist on my server to have access.

    if the same names do exist, the sp_change_user_logins is the way to fix it;

    here's a proc that does it for all users in a particular database:

    use master

    go

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS OFF

    GO

    /****** Object: Stored Procedure dbo.sp_change_users_logins Script

    Date: 2001-10-30 11:51:54 AM ******/

    CREATE PROCEDURE sp_change_users_logins

    @Action varchar(10) -- REPORT / UPDATE_ONE /

    AUTO_FIX

    ,@UserNamePattern sysname = Null

    ,@LoginName sysname = Null

    AS

    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --

    set nocount on

    declare @exec_stmt nvarchar(430)

    declare @ret int,

    @FixMode char(5),

    @cfixesupdate int, -- count of fixes by update

    @cfixesaddlogin int, -- count of fixes by

    sp_addlogin

    @dbname sysname,

    @loginsid varbinary(85),

    @110name sysname

    -- SET INITIAL VALUES --

    select @dbname = db_name(),

    @cfixesupdate = 0,

    @cfixesaddlogin = 0

    -- ERROR IF IN USER TRANSACTION --

    if @@trancount > 0

    begin

    raiserror(15289,-1,-1)

    return (1)

    end

    -- INVALIDATE USE OF SPECIAL LOGIN/USER NAMES --

    if @LoginName = 'sa'

    begin

    raiserror(15287,-1,-1,@LoginName)

    return (1)

    end

    if lower(@UserNamePattern) in ('dbo','guest','INFORMATION_SCHEMA')

    begin

    raiserror(15287,-1,-1,@UserNamePattern)

    return (1)

    end

    -- HANDLE REPORT --

    if upper(@Action) = 'REPORT'

    begin

    -- VALIDATE PARAMS --

    if @UserNamePattern IS NOT Null or @LoginName IS NOT Null

    begin

    raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)

    return (1)

    end

    -- GENERATE REPORT --

    select UserName = name, UserSID = sid from sysusers

    where issqluser = 1 and (sid is not null and sid <> 0x0)

    and suser_sname(sid) is null

    order by name

    return (0)

    end

    -- HANDLE UPDATE_ONE --

    if upper(@Action) = 'UPDATE_ONE'

    begin

    -- CHECK PERMISSIONS --

    if not is_member('db_owner') = 1

    begin

    raiserror(15288,-1,-1,'SA or DBO',@Action)

    return (1)

    end

    -- ERROR IF PARAMS NULL --

    if @UserNamePattern IS Null or @LoginName IS Null

    begin

    raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)

    return (1)

    end

    -- VALIDATE PARAMS --

    -- Can ONLY remap SQL Users to SQL Logins! Should be no need

    -- for re-mapping NT logins, and if you try, you'll mess up

    -- the user status bits! (see samsmith)

    if not exists (select name from sysusers where

    name = @UserNamePattern -- match user name

    and issqluser = 1) -- must be sql

    user

    begin

    raiserror(15291,-1,-1,'User',@UserNamePattern)

    return (1)

    end

    select @loginsid = sid from master.dbo.syslogins where

    loginname = @LoginName -- match login

    name

    and isntname = 0 -- cannot use nt

    logins

    if @loginsid is null

    begin

    raiserror(15291,-1,-1,'Login',@LoginName)

    return (1)

    end

    -- ERROR IF SID ALREADY IN USE IN DATABASE --

    if exists (select sid from sysusers where sid = @loginsid

    and name <> @UserNamePattern)

    begin

    raiserror(15063,-1,-1)

    return (1)

    end

    -- CHANGE THE USERS LOGIN (SID) --

    update sysusers set sid = @loginsid, updatedate = getdate()

    where name = @UserNamePattern and issqluser = 1

    and sid <> @loginsid

    -- FINALIZATION: REPORT AND EXIT --

    if @@error <> 0 or @@rowcount <> 1

    raiserror(15295,-1,-1, 0)

    else

    raiserror(15295,-1,-1, 1)

    return (0)

    end

    -- ERROR IF NOT AUTO_FIX --

    if upper(@Action) <> 'AUTO_FIX'

    begin

    raiserror(15286,-1,-1,@Action)

    return (1)

    end

    -- HANDLE AUTO_FIX --

    -- CHECK PERMISSIONS --

    if not is_srvrolemember('sysadmin') = 1

    begin

    raiserror(15288,-1,-1,'SA or DBO',@Action)

    return (1)

    end

    -- VALIDATE PARAMS --

    if @UserNamePattern IS Null or @LoginName IS NOT Null

    begin

    raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)

    return (1)

    end

    -- LOOP THRU ORPHANED USERS --

    -- declare the cursor explicity global so that the cursor

    -- exists at the end of the exec stmt regardless of the database

    -- option value for 'default to local cursor'

    select @exec_stmt = 'DECLARE Cursor110_Users cursor global for

    select name from sysusers

    where name Like N' + quotename( @UserNamePattern , '''')+

    '

    and issqluser = 1 and suser_sname(sid) is null'

    EXECUTE (@exec_stmt)

    OPEN Cursor110_Users

    WHILE (110=110)

    begin

    FETCH next from Cursor110_Users into @110name

    if (@@fetch_status <> 0)

    begin

    DEALLOCATE Cursor110_Users

    BREAK

    end

    -- IS NAME ALREADY IN USE? --

    -- if suser_sid(@110name) is null

    if not exists(select * from master.dbo.syslogins where loginname

    Like @110name)

    begin

    -- ADD LOGIN --

    execute @ret = sp_addlogin @110name, Null, @dbname

    if @ret <> 0 or suser_sid(@110name) is null

    begin

    raiserror(15497,16,1,@110name)

    deallocate Cursor110_Users

    return (1)

    end

    select @FixMode = '1AddL'

    raiserror(15293,-1,-1,@110name)

    end

    ELSE

    begin

    Select @FixMode = '2UpdU'

    Raiserror(15292,-1,-1,@110name)

    end

    -- REPORT ERROR & CONTINUE IF DUPLICATE SID IN DB --

    select @loginsid = suser_sid(@110name)

    if user_sid(@loginsid) is not null

    begin

    raiserror(15331,-1,-1,@110name,@loginsid)

    CONTINUE

    end

    -- UPDATE SYSUSERS ROW --

    update sysusers set sid = @loginsid, updatedate = getdate()

    where name Like @110name

    if @@error <> 0

    begin

    raiserror(15498,17,127)

    deallocate Cursor110_Users

    return (1)

    end

    if @FixMode = '1AddL'

    Select @cfixesaddlogin = @cfixesaddlogin + 1

    else

    Select @cfixesupdate = @cfixesupdate + 1

    end -- loop 110

    -- REPORT AND RETURN SUCCESS --

    raiserror(15295,-1,-1,@cfixesupdate)

    raiserror(15294,-1,-1,@cfixesaddlogin)

    return (0) -- sp_change_users_login

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    /* finally recreate the users*/

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • It's called "orphaned users".

    To find:

    EXEC sp_change_users_login 'Report'

    To fix:

    EXEC sp_change_users_login 'Auto_Fix', 'Chris', NULL, 'pw'

  • Keep in mind that sp_change_users_login ONLYworks for SQL Server Logins


    * Noel

  • Try using a free tool to move your SQL logons and permissions next time. It's called SQLpermissions. It will create a T-SQL script that you can use to automatically move SQL logons AND permissions from one server to another. You can download it at: http://www.idera.com/freetools

  • There's also a stored procedure you can use to script logins from the old server that you can then run on the new server. It preserves SIDs and passwords so you shouldn't have to clean up orphaned DB users on the destination server. It's not perfect, but it's useful if you need to move logins for which you don't want to change (and don't know) the passwords.

    Google "sp_help_revlogin"; IIRC there's an MS KB article that explains everything.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply