[sims] passwords reset when attaching database

[mcdoodle]

Hi there

 

I have an usual query (I think!)

has anyone every come across passwords in System manager 6 being reset for users when you reattached a database?

We have problems with a database and brought it in to look at it. When the database was attached, all the users had their passwords reset. you could see it in sysman.

I know it is no big deal as we can reset but I was wondering why it happened.

 

has anyone any ideas?

[Janit]

For the passwords to be 'transported' to a new SQL Server, the database must be detached using the DBAttach utility from Capita. DBAttach transfers the passwords from the Master database into the SIMS database ready for transit. You will probably find that not all passwords are reset and it's only those that have been created since the last DBAttach which is probably when SIMS was migrated to SQL 2008.

[Janit]

BTW DBAttach isn't the only method of transferring the passwords as there's also a script you can run but I've never used it.

[mcdoodle]

I have just checked and they shifted it by stopping the services and copying the mdf and ldf. You have been a great help.

Thanks Janit

[Rawns]

BTW DBAttach isn't the only method of transferring the passwords as there's also a script you can run but I've never used it.

 

This can be ran from a command prompt or a batch file (Exclude the [] when putting your info in):

osql -S[iP_Address/Hostname]\[sql_Instance] -U[username] -P[Password] -n -Q"exec [Database_Name].sims.db_p_transfer_login"

 

SIMS:

osql -S127.0.0.1\SIMS2008 -Usa -Ppassword -n -Q"exec sims.sims.db_p_transfer_login"

 

FMS

osql -S127.0.0.1\SIMS2008 -Usa -Ppassword -n -Q"exec ccsfms.sims.db_p_transfer_login"

 

We use these in our backup script even though we backup the databases using dbAttach. There is no harm running the script first. If anything, it acts as a fail-safe to ensure login's are transferred first.

Edited June 24, 2011 by Rawns

[vikpaw]

...We use these in our backup script even though we backup the databases using dbAttach. There is no harm running the script first. If anything, it acts as a fail-safe to ensure login's are transferred first.

 

You should, because from the testing i did, it seems that doing a dbattach backup, doesn't transfer the logins. This is not very useful, so i made a CR for it:

 

1103-855974

 

Please vote if you haven't already done so. I also requested they add a command line / gui option to perform the procedure so that we dont have to run SPs ourselves manually.

 

EDIT: This thread is also really useful to read, from when i did some testing on the issues, the linked post and also another one later on. Basically, it seemed that rather than doing a stopped service file move, if you do a backup and restore the .bak file, then any logins already held in the db would get pushed over.

 

If you have ended up with no logins in your db, maybe just doing a detach/reattach will push whatever has been stored into your db, or could try to restore a backup. Both processes should put some users back. Might be good to try.

 

http://www.edugeek.net/forums/mis-systems/71629-sims-backup.html#post639903

Edited June 26, 2011 by vikpaw
add link