Rawns Posted January 28, 2013 Posted January 28, 2013 I've just read that the SIMS .net re-index database patch "cannot" be applied using the 'SA' account, but instead should be applied using the 'sysman' account instead! Is this correct? If so, why should it not be ran with the 'SA' account?
glen_j Posted January 28, 2013 Posted January 28, 2013 I'd imagine it is due to the sa account being the sql database admin account and not a sims.net admin thus will not have the correct permisssions. All upgrades etc are run using a sims.net account that has system manager rights. You only use the sa account for attaching and detaching the database itself.
Ghostgame Posted January 30, 2013 Posted January 30, 2013 I'd imagine it is due to the sa account being the sql database admin account and not a sims.net admin thus will not have the correct permisssions. All upgrades etc are run using a sims.net account that has system manager rights. You only use the sa account for attaching and detaching the database itself. I don't really get that cos as far as im aware the patch applies changes to the SQL database.
glen_j Posted January 30, 2013 Posted January 30, 2013 it does but you are using a sims application to run the patch so you need to log in as a sims user
GazN Posted January 30, 2013 Posted January 30, 2013 I've run this patch several times through DBUpgrade and have always logged using my sa account and it has worked.
Sdrawkcab Posted January 30, 2013 Posted January 30, 2013 Did this advice come from the capita knowledgebase? Like everyone says, in my experience there is no problem with applying the reindex patch using the SA password. I can't even see why it would - the SA account is completely unrestricted so what permissions could it possibly be missing?
Rawns Posted January 30, 2013 Author Posted January 30, 2013 Did this advice come from the capita knowledgebase? Like everyone says, in my experience there is no problem with applying the reindex patch using the SA password. I can't even see why it would - the SA account is completely unrestricted so what permissions could it possibly be missing? It's in Capitas SIMS Technical Essentials training document that's available on SupportNet!
GazN Posted February 1, 2013 Posted February 1, 2013 I've just looked at the Technical Essentials handbook and page 48 says to run DBUpgrade using SA account or someone who has System Manager ACCESS RIGHTS. It doesn't say to run it as sysman.
CESIL Posted February 1, 2013 Posted February 1, 2013 OK, so I am officially losing the plot...I was scanning the list of posts and swore I read SIMS Reindeer patch...I need a lie down in a dark room...
Greg Posted February 1, 2013 Posted February 1, 2013 Permissions for patches can depend on what the patch changes KB92874 - How to run a patch through DBupgrade states Important Note: If advised to run the patch as the SA user please specify SA as the SIMS Username and enter your SA password KB72431 - SIMS Connection failed for login USERNAME reason 3. When logging into SIMS .net states If it does still appear, please call the Service Desk for a copy of the Reindex patch 14265. This patch must be applied as the Sysman username and password
TheScarfedOne Posted February 1, 2013 Posted February 1, 2013 Hmmm intriguing. I'd never heard that one before either, always run with sa here too without issue
Rawns Posted February 21, 2013 Author Posted February 21, 2013 Just to re-ignite this, had a solution for an issue sent to me from Capita with this as a suggested solution: 2) Apply the patch 14265 (the re-index patch) This patch will re-index all the tables in the SIMS database. This sometimes has a significant effect on the performance of database queries and can help improve the performance of the Discover data transfer. The patch cannot be run as SA user, please run this patch using the 'sysman' SIMS account.
matt40k Posted February 21, 2013 Posted February 21, 2013 (edited) Weird, wouldn't have thought the sysman user had enough access - it changes the recovery model to simple, truncates the log file then sets it back to whatever it was before it started. "SA" or such would have access to do that and reindex tables. I'd be a bit worried actually if sysman could change the recovery model, from a technical point of view - I wouldn't want a user changing something like that - I mean if the data manager had access to change the recovery model to FULL and I thought it was SIMPLE, the log file is going to get very big and cause problems (cause I wouldn't be truncating the log file on doing a backup). Wonder what things it does that needs a SIMS connection - I know the lazy force you to use a Windows user or SIMS SQL user (note not SQL user) to import reports or filesets. Personally I think someone at Capita is reading from an old KB rather than using a bit of common sense and investigating what it does and why – cause it shouldn’t matter what the user is to “reindex” Edited February 24, 2013 by vikpaw language! 1
jinnantonnixx Posted February 22, 2013 Posted February 22, 2013 (edited) The SIMS software uses application roles within SQL (or it did when I last looked - I've drifted out of SQL these days.) In a nutshell, the SIMS application connects to the SQL system whilst logged in as the user. The SIMS application then switches to various application roles as necessary, to do different tasks that require different privileges, providing a password known only to the application. If this is successful, the application's connection loses the user's permissions and executes with the privileges of that application role. So, it's all down to the intent of the software and application roles. Using application roles, it's possible for any user to do (virtually) anything, if the software permits. To the original question - it all depends if the utility is connecting at a server level (using usernames and passwords found in SQL's Security>Users) or application level users (maintained internally by SIMS) I've been away from SIMS for a while, but I think the tool required a server login, (rather than an internal application username) so I would suggest the you would use 'sa'. Edited February 22, 2013 by jinnantonnixx
jinnantonnixx Posted February 22, 2013 Posted February 22, 2013 Suggestion: If it doesn't work as SYSMAN, try it as sa. ;););)
matt40k Posted February 22, 2013 Posted February 22, 2013 Haha lol, yer, what we all do isn't - YOU MUST DO IT AS SYSMAN, oh that didn't work, try it as SA. Application roles - that's the jobbie. The patch is applied via DBUpgrade which if I remember correct either uses Windows Authenication or normal SQL logins if the user is "sa" otherwise it uses application roles. Point still remains sims users - including SYSMAN shouldn't be able to truncate the log file or change the recovery model - I mean if the error was because my recovery model was FULL and my log file had filled my drive - I'd rather fix my backup (so it truncates the log file onces it's done a full backup) so it doesn't happen again.
MACIT Posted March 26, 2013 Posted March 26, 2013 Where can i find this mystical Reindex patch as think it would help with our 9GB database and very slow access to marksheets.
Rawns Posted March 26, 2013 Author Posted March 26, 2013 Where can i find this mystical Reindex patch as think it would help with our 9GB database and very slow access to marksheets. Request it's release from Capita or your local LA support unit, then apply it via SOLUS2.
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now