Column with default value of GETUTCDATE() shows as DBNull

[AndyIII]

When running on my database I get the error message:

Unable to cast object of type 'System.DBNull' to type 'System.String'

I downloaded the source and found that it was the default value query.

The above shows what SSMS returns (getutcdate()), but in the code it gives a String column that returns DBNull.

The difference? I was using a non-privileged user (db_datareader) to do the backup. It tried it with sa and it worked fine.

Please either document what privileges are required to perform the Script action (sorry if I missed the docs), improve the error message, or (my personal preference) BOTH.

[PositiveAndNegative]

This doesn't have to do with GETUTCDATE(), as I've found without the db_ddladmin role default_constraints.definition will return NULL rather than the value for all the defaults, which causes schemazen to crash on (string)dr["DEFAULT_VALUE"]

Maybe it should check IS_ROLEMEMBER ('db_ddladmin') at the start and error out if not

[AndyIII]

I would rather be able to capture some of the settings than to error out and lose everything. Perhaps a warning message and capture the table without the default values? Perhaps something like this:

Cannot retrieve default value for 'LastUpdateTimeUTC'. The user 'MeMyselfAndI' does not have VIEW DEFINITION permission on the table 'TagPalletLocation'. Skipping default value for this field.

This error message tells the user (1) that information is missing, (2) where the information is located so that they can find/fix it later, and (3) what the missing permission is so that they can realize the need to either (a) log in with a different account, or (b) GRANT VIEW PERMISSION on the current user for this table or schema (the db_ddladmin role has permission by default).

As for the code, I think a check for DBNull would suffice, or if you need a super slim code change, how about replacing the cast with .ToString()? I think the DBNull object returns {} or something like that and would be a placeholder for the user to manually edit the script. If you went that route, the warning message should be updated to say manual editing is needed instead of skipping. The advantage of skipping it is that you can then round-trip the database (script, create, script) and the warning would be gone (and it wouldn't have default values).