Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I intend to be using a UNIQUEIDENTIFIER as an access key that users can use to access certain data. The key will act as a password in that sense.

I need to generate multiple such identifiers as part of an INSERT...SELECT statement. For architectural reasons I want to generate the identifiers server-side in this case.

How can I generate a securely random UNIQUEIDENTIFIER? Note, that NEWID would not be random enough as it does not promise any security properties at all. I'm looking for the SQL Server equivalent of System.Security.Cryptography.RandomNumberGenerator because I need unguessable IDs. Anything based on CHECKSUM, RAND or GETUTCDATE would also not qualify.

share|improve this question
@AaronBertrand at the very least, one if the digits is always 4. But the fact that I don't have strong evidence that they might be guessable does not mean that they are not. I cannot base this security decision on this observation. – usr Apr 4 '13 at 14:22
Maybe NEWID is just as random as the "Debian weak SSH keys":… They certainly looked random to the developer who tested them... – usr Apr 4 '13 at 14:39
That 4 tells you what algorithm is used in generating the GUID. – Mr.Mindor Apr 4 '13 at 15:07
You do understand that the only determining factor in the level of your security is the bit entropy? The idea that NEWID is not random enough for your needs would imply that you have some idea of the amount of bit entropy which is required to be "secure" for your use case. What is that number? – Cade Roux Apr 4 '13 at 15:07
Oh I'm just seeing this: "Cryptanalysis of the WinAPI GUID generator shows that, since the sequence of V4 GUIDs is pseudo-random, given full knowledge of the internal state, it is possible to predict previous and subsequent values." So much for "yeah NEWID is probably enough..."! – usr Apr 4 '13 at 15:12
up vote 15 down vote accepted

Should do the trick I would have thought.


Returns a cryptographic random number generated by the Crypto API (CAPI).

share|improve this answer
Perfect, thanks. This is the same API that underlies System.Security.Cryptography.RandomNumberGenerator. – usr Apr 4 '13 at 14:24
Well, this eliminates that pesky 4, that makes NEWID() so guessable. +1 – Aaron Bertrand Apr 4 '13 at 14:26
However, a result like this is not guaranteed to be unique, even if it's random. – GalacticCowboy Apr 4 '13 at 19:51
@GalacticCowboy it is at least as unique as a NEWID and practically 100% unique (when rounding off at 30 decimal digits or so...). My developer time is better spent elsewhere than worrying about this. I'd rather play the lottery. – usr Apr 4 '13 at 20:48
But by that argument, NEWID was adequate too. Given the depth of knowledge required, anyone capable of predicting GUID values in the sequence would already have such complete access to your systems that they wouldn't even have had to bother cracking GUID generation. – GalacticCowboy Apr 4 '13 at 21:02

Just my two cents, but this may not be a good idea. To paraphrase Eric Lippert's excellent series on GUID's (part 1, part 2, part 3), the acronym is GUID, not GSUID - Globally Unique Identifier, not Globally Secure Unique Identifier.

The problem lies in that when GUIDs are generated within a non-hostile scope, such as everyone using NEWID(), all values are guarenteed to be unique (well, sort of, see Eric's article, part 3). But if a hostile entity enters that scope, they can both predict the next generated GUID, as well as cause collisions on their own.

By creating your own method of generating a value that you store inside a structure that looks like a GUID, you have essentially become a hostile entity. You have changed the contract of a GUID from being unique to being random. While someone better at math than I could probably prove you are still unique, that is only within the confines of your generation method. If you mix these pseudo-GUIDs with NEWID() GUIDs, all bets are off.

I say this may not be a good idea only because I don't know the entire scope of how you are using the values. If you are the only entity generating the values (no mix and match), and/or you aren't persisting the values, and/or you don't care about collisions, this may not be an issue. If any of those items aren't true, you may want to re-evaluate.

share|improve this answer
Interesting points. My data type choice UNIQUEIDENTIFIER is mostly a coincidence. It is just convenient to handle a 16 byte quantity using that type. I think of it as a password. It has to be unique, though. I'm confident that I will never see a collision (and even if there is the app will just crash - so that's safe, too). – usr Apr 5 '13 at 11:50
I disagree, it depends 'how' you encrypt it to if it becomes random rather than unique. It can easily be the same. – Dawesi Sep 27 '15 at 12:31

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.