Run proc as user

JamesLast99

Registered User.
Local time
Yesterday, 18:32
Joined
Apr 9, 2009
Messages
128
I have a trigger which EXECs a proc. the proc set indentity insert so I can exolicitly post the IDs to a Key, identity field.I get an error cos the user who fired the triiger hasn't got permissions to use SET to change identity insert.How can I run the proc, via the triiger as someone with the correct permissions.Ta!
 
Thanks - but I should have said I am using 2000.Maybe I can do it entirely differantly.I have a db and a testdb (exact copies) - the data in each is unimportant - except for the fact if a record is added to the staff table in db - it must also be added to the staff table in testdb.Hence my trigger - copies records not in testdb.staff -from db.staff (and does updates) this is why I am explicitly putting in the ID - so I can match what records are not already there, and update the correct ones.If theres a better way - I'm up for that too.Cheers!
 
Thanks for the update, I have a few more questions to ask before I start suggesting other solutions.

Does the testdb have to updated with the new record immediately? or could you update them on a schedule, say every half hour?

Are these databases on the same server?

Is there a 'datecreated' column on this table you could use as a pointer for the schedule?

How many new records are there on average over an hourly period?
 
1) Yes - Immediately 2) Yes - same server3) NO date field.4) Just above 0 - its actual db users, so when new staff start one is added - in practice - none could go on for weeks then 10 in a couple of minutes. So usually nothing - sometimes very few.Ta
 
Hmmm, can you not remove the identity from the test database all together? and leave it as a primary key without identity

it seems like you need to keep the test and live tables in synch which would negate the need for an indentity column on the test database / table.

Another option is SQL server replication, but I suspect that's going to be vast overkill and a lot of hassle for you to set up
 
Thanks - I am just going to remove the identity insert - they should be in sink so the identities should be inserted in synch anyhow.Thanks
 

Users who are viewing this thread

Back
Top Bottom