SQL 2005 and Auto Increment

rrvanzyl

I have just come across a very ineresting "feature". When using the Auto Increment property on a Interger field in a table, and running on a SQL 2005 installation, a user without sys-admin rights gets a very vicious SQL error stating that the user doesn't have permission (even if it was setup and synched with SQL) or the table doesn't exist. This doesn't happen when the user capture data directly on the table, only if you use a indirect method through code or a dataport to insert/modify data in that table. Has anyone else come across this? Is there any way in SQL Server where you have to set a certain setting to cater for this propoerty?

Thanks!

R

ara3n

try this.

http://www.mibuso.com/forum/viewtopic.php?t=7262

kine

1) When you are using Autoincrement, you need to CLEAR the field to 0 before you will insert it into DB. Else this error is called
2) Why? Because if you try to force the value of the Autoincrement field, SQL needs to set the autoincrementing caunter to new value but this can be done just with db_owner privileges. It means, there is error in your code (do not forget, that INIT is not clearing PK fields!).

kriki

[Topic moved from Navision forum to SQL General forum]