Andreas Wolter recently posted yet another reason to keep guest disabled on user databases in SQL Server. He also points out some reasons why developers shouldn’t have access to production systems, but I’d like to focus on the implications for guest. As Andreas summarizes at the end of his post,
“never use the guest account for data that is not really supposed for everyone.”
Absolutely agree! Guest is disabled by default in all user databases and should remain so – guest really does mean everyone. There is no way to keep someone that has access to SQL Server from leveraging an enabled guest account – that is how guest is designed to work. No planned changes will alter this guidance. If you need broad access to a database but with some exceptions, it is preferable to use Windows group accounts with broad membership to provide that access and then deny as needed. For SQL authenticated users, explicitly provision the individual logins which need access.
I should also point out that guest is needed for the proper functioning of some of our system databases – such as tempdb. But here the situation is that everyone on the SQL Server instance really does need access to this database for temporary objects. See Buck Woody’s post Don’t mess with the system databases in SQL Server, or Error: 916 for more information.
Many applications need to generate random data, and in order to help in this task they typically rely on pseudorandom number generators (PRNG). Typical PRNGs are deterministic in nature and therefore they are not cryptographically suitable, this is the case of the built-in RAND (http://msdn.microsoft.com/en-us/library/ms177610.aspx) in SQL Server.
If your T-SQL application needs to use a cryptographically secure PRNG (CSPRNG), an alternative is to use CRYPT_GEN_RANDOM (http://msdn.microsoft.com/en-us/library/cc627408.aspx). As the documentation online suggests, this builtin is pretty much a T-SQL wrapper around the Crypto API (CAPI) function CryptGenRandom (http://msdn.microsoft.com/en-us/library/aa379942.aspx) using the Microsoft CSP.
Since CRYPT_GEN_RANDOM return value is a varbinary it can easily be consumed as such (binary data) or converted to any T-SQL data type compatible with such conversion, such as int and bigint, for example:
SELECT crypt_gen_random(4)
SELECT convert( int, crypt_gen_random(4)) SELECT convert( bigint, crypt_gen_random(8))
For more detailed information on how the CryptGenRandom works, please see the remarks section on the CryptGenRandom documentation online at: http://msdn.microsoft.com/en-us/library/aa379942(VS.85).aspx.
NOTE: A quick word of warning when converting to some data types such as varchar or nvarchar, the output may contain invalid (or unprintable) characters.