Recently I was mystified by an issue with one of my Web applications in which the IDENT_CURRENT() function was returning null. I was under the impression that this function (which returns the last identity value generated for a specific table or view) would always return a value. In fact, as pointed out in this post, SQL Server 2005 (and presumably also later versions) requires the database user to have ALTER, CONTROL, DELETE, INSERT, REFERENCES, SELECT, TAKE OWNERSHOP, UPDATE or VIEW DEFINITION permissions on the underlying table, otherwise IDENT_CURRENT() will return null.
I can’t recall exactly how the permissions were set up in my case, but I know that the database user had SELECT permission on the entire database, and therefore was able to SELECT from the table in question – let’s call it ‘articles’. However, IDENT_CURRENT(articles) only started returning values other than null when I explicitly granted SELECT permission to the user for the specific ‘articles’ table.