If you haven’t used it (and I rarely do myself), Ident_Current (‘tablename’) returns the last identity value generated across all sessions. It’s something to use carefully, and not fall into the trap of thinking that if Ident_Current = 2 and you insert a row that the value will be 3 – someone could have inserted (or tried to) in the interim and used the value of 3.

But caveats aside, what would you expect to happen in these two cases?

• Table name doesn’t actually exist
• The caller doesn’t have permissions on the table

For the first case it returns a null, and for the second case too! Not sure I agree with failing to throw a true error, and I’ve got a friend that agrees after spending some time ‘fixing’ code that had worked fine when built in a development environment but then failed when run under other credentials. A nice obvious error message would have been appreciated!

So why isn’t one raised? I didn’t know, started with BOL, and what they say is:

In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_CURRENT may return NULL if the user does not have any permission on the object.

If you read this about Metadata Visibility Configuration this was part of changes made in SQL 2005. Prior to that metadata was visible to anyone in the public role. It’s another step in reducing the ways hackers get information that might lead to a deeper attack.

We could tease my dev friend about insufficient testing, but I can see it’s an easy thing to miss, especially for ‘job’ type code. I think what he (and I) find frustrating is that we expect to code execute successfully or raise an exception. Testing return values is easy enough to do, but it’s a less used – or less expected – technique.

How could we do it better? Testing, sure. But imagine a case where an employer has had code running under SA for years. You undertake the cleanup, run it under a lesser account, no errors, must be good! I know, testing, again. But think how much quicker it would be to find and fix if you could see the error. The only idea I have for that would be to raise the error to Profiler but not to the caller, which doesn’t seem all that intuitive either.

At least, maybe this is one pothole you and I won’t step in!