Search code examples
sql-serverdefault-valuessms-2012

Why does my SQL Server column appear to have no default value even though it acts like it does?

I have a SQL Server table that has four columns in it, one of which is a datetime column with a default value of getdate(). I have two copies of this table, one in a development database server over which I have full control, and another in a production database server in which I have few permissions.

Here is how the development table looks:

enter image description here

I've selected the dtInsert column. Notice that this column has a default value of getdate(). The production version I have of this table is exactly the same. When I add a row to this table, the dtInsert cell defaults to getdate() like I'd expect. When a database administrator generates a script of the production table, it includes the default value constraint. However, when I view the table design in SQL Server Management Studio 2012, it shows the column as not having a default value. See here:

enter image description here

When I generate a database diagram, it also shows the dtInsert column as having no default value. Again, I know from testing that the dtinsert column in my production database server indeed defaults to getdate().

Is this a bug in SQL Server Management Studio version 2012? Is there some permission I don't have which brings about this behavior? Is it something else? Why does the column appear to have no default value even though it does?

Solution

  • Is this a bug in SQL Server Management Studio version 2012?

    No.

    Is there some permission I don't have which brings about this behavior?

    Yes.

    In a comment on the question I suggested running the following:

    SELECT *
FROM sys.default_constraints
WHERE [parent_object_id] = OBJECT_ID(N'_table_name_');

    The result was a row return in Production in which the [definition] column was NULL. This means that the DEFAULT CONSTRAINT is there but you either:

    • lack explicit and implied permissions to see the definition

    • have been explicitly denied the permission to see it.

    You can read up on this on the MSDN page for Metadata Visibility Configuration.

    Now, there are various permissions (VIEW DEFINITION, VIEW ANY DEFINITION, etc.) that affect this setting. These can be applied at various levels:

    • the object itself
    • the schema
    • the database
    • etc

    Permissions get further complicated when taking into account membership in multiple Windows Groups (if those are being used).

    The permission can even be granted at multiple levels. Permissions are also additive: a GRANT in 1 out of 3 Windows Groups that your Login is a member of is enough to work. However, a DENY in any of those levels takes precedence and in that case, no definition for you.

    As I mentioned in a comment on the question, this is really a matter for the Production DBA(s) who configured permissions such that you can't see the definition. Without know exactly why you can't see the definition (lacking of a GRANT or presence of a DENY?) it is useless issuing GRANT statements trying to get this permissions (especially since not being able to see the definition implies that you likewise would not be able to GRANT such permission to anyone). Please go talk to whoever is in charge of Production telling them that you can't see the definition of a default constraint, but you would like to be able to. If there is a specific reason why you currently cannot, you will be told. If it is an oversight, they should correct it in a controlled fashion that might need to be replicated to other environments, etc.