Re: restoring dB and login to new server

Perhaps sp_help_revlogin is useful? (Or maybe not, I missed beginning of the thread.) Google for it, and you find the KB.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"geekyguy" <geeky@xxxxxxx> wrote in message news:OMQcrmjqIHA.1952@xxxxxxxxxxxxxxxxxxxxxxx
"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

  • restoring dB and login to new server
    ... 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. ... 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". ...
    (microsoft.public.sqlserver.tools)
  • Re: Any Help Please
    ... So in fact when you login as User1, even though User1 exists as a user in the database, the login User1 and the user User1 are not related. ... I had first moved the logins and that seemed to work fine then upon restoring the databases and leaving them at 8.0 compatibility the explicit object permissions are not restored. ...
    (microsoft.public.sqlserver.security)
  • RE: Stored Procedure Question regarding users
    ... > You can run the procedure as is substituting Pubs with your database name. ... >> was restored to a new SQL server. ... >> logins for the users who already exist in the database that I am restoring, ... Automatically map a user to a login, creating a new login if necessary ...
    (microsoft.public.sqlserver.server)
  • After database restore, cannot set a login for access to database
    ... After restoring a database backup to another server, ... empty 'login name'. ... but query: 'select * from staging.employees' will be ok. ...
    (microsoft.public.sqlserver.security)
  • Stored Procedure Question regarding users
    ... I understand that the database being ... Now that I am restoring the database to a new SQL server instance (with the ... Automatically map a user to a login, creating a new login if necessary ...
    (microsoft.public.sqlserver.server)