Re: Resynching DB Users after Restore

From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 11/04/04

  • Next message: Mike Epprecht \(SQL MVP\): "Re: Logout takes 20 minutes?" 
    Date: Thu, 4 Nov 2004 15:42:22 -0500

    Not really. The users are linked via SID, not login name to avoid an
    accidental security breach due to a cross-server restore. You have to fix
    the SIDs somehow. You can use sp_change_users_login inside a script to do
    this semi-automagically, especially if you have a small, known set of logins
    and users to remap. 

    -- 
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dave" <Dave@discussions.microsoft.com> wrote in message
news:5157AF83-2A2E-421D-BD49-AB6740AD00D9@microsoft.com...
> Is there any option or trick where we can aviod this while restoring
database
> from another server. I mean, where they restored with database adn we
don't
> have to add / drop users.
>
> "Geoff N. Hiten" wrote:
>
> > Look up sp_change_users_login.  It does exactly what you need.
> >
> > -- 
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Tom" <Tom@discussions.microsoft.com> wrote in message
> > news:1C5B610B-09AE-47A5-A264-D8C399A4A725@microsoft.com...
> > > I know one has to sync up the database users to the server logins when
you
> > > restore a database from another db server.  I generally do this by
> > dropping
> > > and readding users.  I have a problem when the user is aliased -
> > sp_dropalias
> > > doesn't work.
> > >
> > > Is there a way to realias a user? The only thing I've seen in research
> > > newsgroups  is to delete the row in the sysusers table.
> > >
> > >
> >
> >
> >
  • Next message: Mike Epprecht \(SQL MVP\): "Re: Logout takes 20 minutes?"

    Relevant Pages

    • Re: unable to start SQL Service ... error code 3417
      ... How about just create a new instance and restore databases to the new instance? ... I wouldn't go with restoring master from another instance. ... The supported route is to rebuild your master database, start SQL Server in single user mode, ...
      (microsoft.public.sqlserver.server)
    • Re: SQL 2000 SP4 on 2003 Server - Single User Mode
      ... I found the section where you list the filename to restore to. ... rename the SQL Server the same as what is listed below Courthouse? ... Once you've created your database (simply right click on the Database ...
      (microsoft.public.sqlserver.setup)
    • Re: Moving databases from SQL 7 to SQL 2000 - More Questions!
      ... want to run sp_updatestats after the restore or attach as well. ... >>There is a lot more that can go wrong with the wizard ... > SQL Server Databases ... >>Issues When a Database Is Moved Between SQL Servers ...
      (microsoft.public.sqlserver.setup)
    • Re: HELP!!! restore database - left in loading state?? PLEASE H
      ... RESTORE DATABASE WITH RECOVERY ... Columnist, SQL Server Professional ...
      (microsoft.public.sqlserver.tools)
    • Re: Restore Master DB to New Server
      ... the user database will be suspect. ... Just drop them and then do the restore. ... Tibor Karaszi, SQL Server MVP ...
      (microsoft.public.sqlserver.tools)