[SIMS] SIMS Reindex Patch

[Rawns]

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]

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]

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]

it does but you are using a sims application to run the patch so you need to log in as a sims user

[GazN]

I've run this patch several times through DBUpgrade and have always logged using my sa account and it has worked.

[Sdrawkcab]

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]

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]

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]

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]

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]

Hmmm intriguing. I'd never heard that one before either, always run with sa here too without issue

[Rawns]

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]

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”

[jinnantonnixx]

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

[jinnantonnixx]

Suggestion: If it doesn't work as SYSMAN, try it as sa.