Ident_Current returning NULL on SQL 2005

Question

The ident_Current function is returning a null on sql server 2005. After looking at some documentation it looks like it needs db_owner permissions to have visibility meta data, but thats only specified in documentation for sql server 2008..

can someone confirm that its the same for sql server 2005 ? , i cant find any documentation to confirm this ..

many thanks

Answer 1

IDENT_CURRENT() will return null if the function is run under an account that does not have permissions to select the last id for a table or view.

Another thing to bear in mind when using IDENT_CURRENT() is that the id returned can be for any scope and any session; this may be intentional on your part, but I usually use SCOPE_IDENTITY() when I need to obtain an id for the last manipulated record

Answer 2

Russ is right. I want to point out that we had a developer use Ident_current to get the last identity and his proc ran at the same time as a dataimport and he got the identity of the record the import was running and this messed up the data integrity in the database as the child table was now related to the wrong record. It was a reall problem to find and fix, too. So be very careful using ident_current. In most cases scope_identity() is what you want.

Answer 3

to use ident_current or any of the other functions to get the last identity, the user needs to have permissions to read meta data, so it needs db_onwer on that database.