+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
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 ...
  1. #1
    MattGibson's Avatar
    Join Date
    May 2008
    Location
    Epsom
    Posts
    183
    Thank Post
    25
    Thanked 16 Times in 11 Posts
    Rep Power
    16

    Angry 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....?

    Any ideas?

  2. #2

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,522
    Thank Post
    374
    Thanked 677 Times in 551 Posts
    Rep Power
    166
    Re-run S:\setups\SIMSSQLApplicationSetup.exe

    Check where it installs too, some times it refers to the old path (MSDE).
    There are known issues with dbattach with switches.

  3. #3

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,522
    Thank Post
    374
    Thanked 677 Times in 551 Posts
    Rep Power
    166
    Quote Originally Posted by jinnantonnix View Post
    Try running this code against the database (at your own risk, of course) from a query in SQL Manager.

    use [your_database_name]
    exec sims.db_p_install

    This should fix the users / logins.
    That won't fix it. SA isn't a SIMS user account, and it isn't an sa issue.

    Handy to know tho.

  4. #4
    MattGibson's Avatar
    Join Date
    May 2008
    Location
    Epsom
    Posts
    183
    Thank Post
    25
    Thanked 16 Times in 11 Posts
    Rep Power
    16
    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

  5. #5
    MattGibson's Avatar
    Join Date
    May 2008
    Location
    Epsom
    Posts
    183
    Thank Post
    25
    Thanked 16 Times in 11 Posts
    Rep Power
    16
    How do i do this as sa isn't part of the system manager.

    Do i use ChSaPwd.exe?

  6. #6

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,522
    Thank Post
    374
    Thanked 677 Times in 551 Posts
    Rep Power
    166
    Log on to the SIMS\MSSQL server as administrator and run...

    OSQL -E -S servername -Q "exec sp_password NULL, 'newpassword', 'sa'"

  7. Thanks to matt40k from:

    MattGibson (12th November 2008)

  8. #7
    MattGibson's Avatar
    Join Date
    May 2008
    Location
    Epsom
    Posts
    183
    Thank Post
    25
    Thanked 16 Times in 11 Posts
    Rep Power
    16

    Smile

    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.

  9. #8

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,522
    Thank Post
    374
    Thanked 677 Times in 551 Posts
    Rep Power
    166
    You mean MSSQL [Quickly covers up his documents with MySQL instend of Mssql]

    I would try reinstalling dbattach.

  10. #9
    MattGibson's Avatar
    Join Date
    May 2008
    Location
    Epsom
    Posts
    183
    Thank Post
    25
    Thanked 16 Times in 11 Posts
    Rep Power
    16
    Me again!

    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?

    cheers guys

  11. #10

    Join Date
    Apr 2006
    Posts
    390
    Thank Post
    23
    Thanked 95 Times in 61 Posts
    Rep Power
    45
    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.

    -S servername

    vs

    -S servername\instancename

    Also - am assuming that you are running the commands from the same machine that is running the backup script, is that correct?

  12. #11
    MattGibson's Avatar
    Join Date
    May 2008
    Location
    Epsom
    Posts
    183
    Thank Post
    25
    Thanked 16 Times in 11 Posts
    Rep Power
    16

    Question

    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

  13. #12

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,522
    Thank Post
    374
    Thanked 677 Times in 551 Posts
    Rep Power
    166
    :S

    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

  14. #13
    MattGibson's Avatar
    Join Date
    May 2008
    Location
    Epsom
    Posts
    183
    Thank Post
    25
    Thanked 16 Times in 11 Posts
    Rep Power
    16
    Quote Originally Posted by matt40k View Post
    :S

    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
    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......

  15. #14
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    1,003
    Thank Post
    60
    Thanked 167 Times in 119 Posts
    Rep Power
    69
    Quote Originally Posted by MattGibson View Post
    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
    You could do this under a scheduled maintenance plan in SQL Server Management Studio.

  16. #15
    MattGibson's Avatar
    Join Date
    May 2008
    Location
    Epsom
    Posts
    183
    Thank Post
    25
    Thanked 16 Times in 11 Posts
    Rep Power
    16
    Quote Originally Posted by matt40k View Post
    :S

    DBCC CHECKDB WITH NO_INFOMSGS, TABLERESULTS
    Reported no errors. Only took about 5 Seconds!



SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. SIMS user permissions report
    By User3204 in forum MIS Systems
    Replies: 14
    Last Post: 13th November 2009, 01:52 PM
  2. User Profiles theme problem.
    By jamin100 in forum Windows
    Replies: 26
    Last Post: 29th August 2008, 11:19 AM
  3. User / user manager problem after migration
    By SimpleSi in forum EduGeek Joomla 1.5 Package
    Replies: 5
    Last Post: 9th June 2008, 11:57 PM
  4. SIMS User Groups and Permissions
    By messedupeffect in forum MIS Systems
    Replies: 5
    Last Post: 13th November 2007, 05:02 PM
  5. User moving problem - RM CC3
    By Tuxguy in forum Windows
    Replies: 5
    Last Post: 17th August 2006, 02:00 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •