CODEWITHSUNDEEP
sql-server
np-hard
np-hard

Reputation: 5815

Ident_Current returning NULL on SQL 2005

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 metadata, but thats only specified in documentation for sql server 2008.

Can someone confirm that its the same for sql server 2005? I can't find any documentation to confirm this.

Upvotes: 1

Views: 2558

Answers (3)

np-hard
np-hard

Reputation: 5815

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.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96610

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.

Upvotes: 0

Russ Cam
Russ Cam

Reputation: 125528

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

Upvotes: 5

Related Questions