mssql set identity_insert on

[Davidiam]

As part of our release process, we need to insert records in the auth tables using specific id's.  On sqlite this is no problem, but in mssql we get an error saying insert_identity is off.
The solution is to set insert_identity ON, but I am not sure how to do this using the web2py DAL.  Can any give me an code example for this?

We are basically inserting user and group records using ids from a master table.

This is the command that I would need to run before doing the inserts :

SET IDENTITY_INSERT dbo.auth_user ON;  

Thanks,
David

[Dave S]

I can't, because I'm using a different DB, but I remember thinking at the time of your earlier post about IDs that ... this is wrong.  I think your process should be using roles, not IDs, and this is what the auth_group table is for.  Perhaps if I understood your process, I would agree that relying on specific IDs is the right answer, but it is going to take some effort to convince me.

/dps

[Davidiam]

I agree that this is not good practice, but it is the only workable solution that we have found for multi pillar release.

We are also heavily using roles, but here the problem is guaranteeing that users have the same ids on all pillars, otherwise we risk users belonging to the wrong groups in production

I have found a work around to the insert problem by reserving a range of user records and doing updates instead of inserts; also not a clean solution.