Re: restoring dB and login to new server

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

"Tom Moreau" <tom@xxxxxxxxxxxxxxxxxxxx> wrote in message news:uDu0sVjqIHA.2188@xxxxxxxxxxxxxxxxxxxxxxx
Check out sp_change_users_login in the BOL.

Thanks Tom, that looks like it would simplify things a bit...but I still need to create the login manually? I don't mind doing this, except that it introduces fat-finger errors like mistyping the password.


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"geekyguy" <geeky@xxxxxxx> wrote in message
news:%235s5EGjqIHA.1240@xxxxxxxxxxxxxxxxxxxxxxx
Hi All: I have a basic question about SQL 2005 security...I often transfer
databases between servers by doing a full backup, then restoring on the new
server through SMS. These are website databases that have a SQL login
associated with the connection string.

The restore works fine, but I then need to recreate the SQL login on the new
server, and if I try to add the same login in SMS and map it to the
database, I get an error that the "user or role already exists". My
workaround is to delete the database user in SMS, and then map the login
back to the database. Is there an easier way to restore the login associated
with the user account? Or is there no way to transfer logins at the same
time I'm restoring the dB?




.

Relevant Pages

  • Re: Install MSDE w/ MSDE Depl.Toolkit. What permissions when using Win Auth?
    ... I created a login using Enterprise Manager on SQL Server on my server. ... Database Access tab I ticked the tick box for the database that I ... Now when my user installed the MSDE database on his machine locally, ...
    (microsoft.public.sqlserver.msde)
  • Re: SQL Server 2005 Express Remote connection
    ... I have also deleted the login for Fred from the list of users for my ... So Fred no longer exists as a login or as a user in the SQL Server setup. ... the database? ...
    (microsoft.public.sqlserver.server)
  • Re: Installation OK, but cant connect
    ... > created an access database. ... SQL Server authentication is "client" independent.... ... connections or SQL Server authenticated connections... ... which authenticate user's login at the windows login step... ...
    (microsoft.public.sqlserver.msde)
  • Re: Moving a database to another server
    ... onto a server at home. ... The database has a user that has public, db_owner, ... SQL Server login or a Windows login. ...
    (microsoft.public.sqlserver.server)
  • Re: Setting up Linked server to MsAccess
    ... At the server level I have added my windows user group ... At the database level I have added my Server login ...
    (microsoft.public.sqlserver.security)