MIS Systems Thread, Sims SA user problem in Technical; Very strange problem i went to do a backup last night but couldn't get the SA user to work. so ...
12th November 2008, 10:33 AM #1
Sims SA user problem
Very strange problem i went to do a backup last night but couldn't get the SA user to work. so i checked nightly backup script to see what password it was using, and sure enough it was correct. So how comes its backing up with SA user but im unable to use it under DBAttach....?
12th November 2008, 10:37 AM #2
Check where it installs too, some times it refers to the old path (MSDE).
There are known issues with dbattach with switches.
12th November 2008, 11:03 AM #3
That won't fix it. SA isn't a SIMS user account, and it isn't an sa issue.
Originally Posted by jinnantonnix
Handy to know tho.
12th November 2008, 11:34 AM #4
the exact message i get is "Login Failed for user 'sa'."
Cant really understand if its backing up with sa - surely it should work for other database tasks such as dbattach
12th November 2008, 12:49 PM #5
How do i do this as sa isn't part of the system manager.
Do i use ChSaPwd.exe?
12th November 2008, 12:58 PM #6
Log on to the SIMS\MSSQL server as administrator and run...
OSQL -E -S servername -Q "exec sp_password NULL, 'newpassword', 'sa'"
Thanks to matt40k from:
MattGibson (12th November 2008)
12th November 2008, 01:30 PM #7
Ah Brilliant, Cheers for that it worked - My SQL knowledge is very limited.
Don't understand how it could use the same login on the script to do a backup. but wouldn't let me use it anywhere else.
12th November 2008, 01:42 PM #8
You mean MSSQL [Quickly covers up his documents with MySQL instend of Mssql]
I would try reinstalling dbattach.
19th November 2008, 01:13 AM #9
Since changing the sa password my backups are now failing, When the SQL Script runs it says failed login for Sims & FMS.....
I have looked at the script under -Usa -Ppassword and its is the same password as i changed it too the other week.
Is there another table in the database which needs updating?
19th November 2008, 09:06 AM #10
Have you got multiple instances of SQL going on there?
Is the instance name in the backup script the same one that you specified in the password change command, i.e.
Also - am assuming that you are running the commands from the same machine that is running the backup script, is that correct?
19th November 2008, 05:12 PM #11
Yep Double checked its the same as what i used.
OSQL -E -S ADM-Sr-001\Sims2005 -Q "exec sp_password NULL, 'newpassword', 'sa'"
And this seemed to work.
Like i said before the backup script worked fine before i changed the sa password because i was unable to use DBAttach.
The script I'm using is:
OSQL -S ADM-Sr-001\Sims2005 -E -N -Q "exec ccsfms.sims.db_p_transfer_login"
OSQL -S ADM-Sr-001\Sims2005 -E -N -Q "exec sims.sims.db_p_transfer_login"
OSQL -S ADM-Sr-001\Sims2005 -Usa -Ppasswordsql-2005 -n -Q "BACKUP DATABASE SIMS TO DISK = 'F:\Program Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\Nightly\SIMSSQL.BAK'"
OSQL -S ADM-Sr-001\Sims2005 -Usa -Ppasswordsql-2005 -n -Q "BACKUP DATABASE CCSFMS TO DISK= 'F:\Program Files\MICROSOFT SQL Server 2005\MSSQL.1\MSSQL\NIGHTLY\CCSFMS.BAK'"
net stop mssql$sims2005
net start mssql$sims2005
19th November 2008, 05:18 PM #12
Why are you stopping the service and starting it? Backups like this are done live
I would run a query (using SQL Server Management Studio ) to check for any errors.
DBCC CHECKDB WITH NO_INFOMSGS, TABLERESULTS
Check there is no errors in the database. I would really look at doing this via dbattach with switches.
Otherwise, just stop the service then copy the .mdf and .ldf
19th November 2008, 05:25 PM #13
Thats who set it up before me, I thought that was a bit strange. Will CheckDB later. i think our DB needs a good sorting out......
Originally Posted by matt40k
19th November 2008, 05:39 PM #14
You could do this under a scheduled maintenance plan in SQL Server Management Studio.
Originally Posted by MattGibson
20th November 2008, 10:44 AM #15
Reported no errors. Only took about 5 Seconds!
Originally Posted by matt40k
By User3204 in forum MIS Systems
Last Post: 13th November 2009, 01:52 PM
By jamin100 in forum Windows
Last Post: 29th August 2008, 11:19 AM
By SimpleSi in forum EduGeek Joomla 1.5 Package
Last Post: 9th June 2008, 11:57 PM
By messedupeffect in forum MIS Systems
Last Post: 13th November 2007, 05:02 PM
By Tuxguy in forum Windows
Last Post: 17th August 2006, 02:00 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)