SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

A Surprising Result from Ident_Current

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!


I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.


Posted by Merrill Aldrich on 6 October 2010

I also found a bug around just this issue a couple of years back. Thought I was hallucinating :-). Glad to know, in a way, that I wasn't the only one...

Posted by Andy Warren on 6 October 2010

Well, there are least two that have hit it:-) Thanks for reading and taking time to comment.


Leave a Comment

Please register or log in to leave a comment.