Hi there, we have moved TM to a different server do to an SBS upgrade. Currently, we have SQL 2000 Ent with TM8. The database was detached, backed up and moved to other server. Then we reattached it and ran the orphan script. We can log in as any user, but so far nobody can access actual data and bring up Matters or Contacts or anything. I can however manage users.
Also, I tried running the database maintenance and it fails starting with not being able to find the "Job Template".
What are we missing? So far we are down and any help would be great.
I've had this same issue after a server crash reattaching the database from a backup.Read here:http://www.lexisnexis.com/community/pmforums/forums/t/40922.aspxI had to play around with the TM7USER permissions. Even SYSADMIN role didn't work. I had to give specified permission on most all tables.
Michael W. Gaines Jr. Gaines Database ConsultingTime Matters CIC Florence, SC 29501 (843) 615-8084 email@example.com://www.gainesconsulting.com Proud Southeastern Affiliate of Eastern Legal Systems, LLChttp://www.easternlegalsystems.com
Ryan I just realized the link I provided is in a private forum. Just be sure TM8USER is the dbowner and manually give select/write permissions to TM8USER on the Matter and Contact tables, then test. You should begin seeing records populate the list. When I resolved the issue it was more of a "Hey it's working, but I'm not sure what I did" kind of thing.
Most of this is unnecessary and a CIC who is experienced with SQL Server will be able to resolve this very quickly. The reason that most SQL issues are not discussed openly here is purely due to the same reason that lawyers don't post legal advice in public forums. It isn't that they don't want to do so, it is because of the potential liability issues.
This is a perfect example of what I mean: How does one go about granting permissions for TM8USER to only the Matter and Contact tables but not the other tables in the database (MATTER2, MATTER3, CONTACT2, CONTACT3, EVENTS, etc.?) If we only give rights to selected tables then what happens when we try to create events?
The answer is a simple SQL statement we use all of the time, but may not be appropriate here. The command would be:
EXEC sp_change_users_login 'Auto_Fix', 'tm8user'
But giving this advise is like an attorney answering a question about how to defend a capital murder case pro se when he or she does not have any of the facts. "I was appearing live on American Idol when the murder took place" might be an important piece of information to have.
I'm not trying to short change anybody here, I'm just suggesting that one should be very careful getting under the hood with SQL Server if you don't know what you are doing with it.
Mark Deal (CIC/Moderator)Document & Data Solutions, LLCTMTools, LLCAtlanta, Georgiahttp://firstname.lastname@example.org(770) 888-1940Time Matters | HotDocs | PCLaw | TMTools | DDSLink | Worldox
I thought it would be informative to update this thread.
I went on-site and was able to compare the settings for their tm8user with another of my client's (they have an older server and multiple versions of TM still in SQL). I removed all check boxes that did not match up. I believe the one that was directly causing the problem was "sysadmin". Unchecking that caused the data to show back up. The default columns for the All tab were missing but were easy enough to add back in.
I hope this helps.
Legal Technology Support Specialist Time Matters/Billing Matters CIC, Timeslips CC A+ Certified
MicroSupport, Inc. 602-692-7300 Office
Michael, it's funny that you posted this because I went through this again with another client who had motherboard fail last night until close to 9pm and was able to pinpoint exactly what needed to be done to get the restore to work on the new SQL Server installation like normal. I also put together a script to perform the steps. The following script/steps will place the TMUser configuration in the correct setup to display records and list columns as before:
1. Login as sa and restore TimeMatters9 database
2. Execute script:
ALTER AUTHORIZATION ON SCHEMA::[tm9user] TO [sys]
DROP USER [tm9user]
4. Execute Script
CREATE LOGIN [tm9user] WITH PASSWORD=N'EnterPasswordHere', DEFAULT_DATABASE=[TimeMatters9], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER [tm9user] FOR LOGIN [tm9user]
ALTER USER [tm9user] WITH DEFAULT_SCHEMA=[tm9user]
ALTER AUTHORIZATION ON SCHEMA::[tm9user] TO [tm9user]
EXEC sp_addrolemember N'db_owner', N'tm9user'
Just update version number and password. I know it works because I followed these exact steps several times last night trying to get replication setting back using "Preserve replications setting" in the restore window. Each time TM worked flawlessly. Wish I had figured this out the time through this issue.
Also to update the thread, the EXEC sp_change_users_login 'Auto_Fix', 'tm9user' didn't work with either restore project.