Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Gyto
Posting Yak Master
144 Posts |
 Posted - 2010-04-08 : 10:29:03
|
| Hi there,Can someone tell me what's the best way to keep your SQL logins backed up? I know backing up the Master database will do this but am I right in thinking if you do it this way and have to restore it to a new server then it can be difficult?The only other option I can think of is to script them and save the output in a text file so you can just run it again on a new server if you need to restore. The only problem is that means every time you add new logins you have to run the script again to update the list.Any thoughts?Thanks,Matt |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2010-04-08 : 11:39:07
|
| Thanks pk....but here's the question....If your SQL Server was irrepairably broken and you had to start again with a new SQL server (using BAK files to restore all your databases), would you:A) Restore a backup of the Master database from the old server (from a BAK file)...I hear this is tricky?!;B) Keep a script that will recreate all your old logins for you ready just in case this happened;C) ...a better way?This is assuming your old server literally will not work at all and everything needs starting again from scratch...Cheers |
 |
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2010-04-09 : 04:31:41
|
| Just out of interest...can this be resolved by using 'EXEC sp_change_users_login'?...and if so would you need to run this for every individual login and specify all of the passwords again? |
|
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-04-09 : 04:51:41
|
quote:
Originally posted by Gyto
Thanks pk....but here's the question....If your SQL Server was irrepairably broken and you had to start again with a new SQL server (using BAK files to restore all your databases), would you:A) Restore a backup of the Master database from the old server (from a BAK file)...I hear this is tricky?!;
I seriously doubt that restoring of master database may not be possible (I think so but may be possible for Super DBA)quote:
B) Keep a script that will recreate all your old logins for you ready just in case this happened;
It will be more easy way of handling the logins.quote:
Just out of interest...can this be resolved by using 'EXEC sp_change_users_login'?
sp_change_users_login cannot be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN.Regards,BohraI am here to learn from Masters and help new bees in learning. |
|
|
|
|
|
|
Backing up SQL Server Logins