detach & reattach sql logins

[DLTaylor]

I need to rebuild a database.
To do this in need to detach my logins > then recreate db > then reattach logins.

What is the best way to detach and re attach the logins?

 

[yelworcm]

Look up sp_addlogin in BOL. Pay special attention to the @sid parameter.

 

[DLTaylor]

Thanks for the pointer.

My real issue stems from the need to remove the logins and then reattach the to the same rebuilt database.

It would be easy if I had a second db that I could move loggings into on a temp basis and then transfer back, but I do not have this luxury - is there a way of detaching loggings and re-attaching without the help of a second db to store the logins?

Thanks
Dan

 

[yelworcm]

Do you mean change the default database of each login? That is easy enough to do as well. Remember, all of the logins' information is stored in the master database. In going from server to server, the biggest challenge is to keep the sids lined up.

 

[SQLBill]

You might be able to script off the logins from Enterprise Manager and then run the script on your rebuild. But there isn't any 'detach/attach' like there is for databases.

-SQLBill

Posting advice: FAQ481-4875

 

[icemel]

THIS IS HOW YOU DO IT, look no further:

1.) Script the logins on the ORIGINAL server, using "sp_help_revlogin" stored procedure.

2.) Copy and paste the output of the script to Query Analyzer on the TARGET server.

3.) Restore you database on the TARGET server.

Your logins/users will then be attached!

Very simple. No need to run sp_addlogin, etc, etc.

 

[icemel]

Also, if you want to understand this more in depth:

http://support.microsoft.com/default.aspx?scid=kb;en-us;246133

But the above steps work great. I've done it quite a bit myself.

 

[icemel]

Also, see this very excellent article:

http://databasejournal.com/features/mssql/article.php/2228611

Every DBA should know this!