3

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?

user2023861
  • 8,030
  • 9
  • 57
  • 86
  • My guess is a permissions issue - but which permissions I'm not sure of. My google-fu is not strong enough at the moment. – Kritner Dec 16 '14 at 19:10
  • I believe you would need DDL permissions on the object to see the default value. db_owner or db_ddladmin would do it. – Dave.Gugg Dec 16 '14 at 19:21
  • 1
    Seems odd that you can see the columns but not constraints? Try this: `SELECT * FROM sys.default_constraints WHERE [parent_object_id] = OBJECT_ID(N'_table_name_');` in both environments to see if there is a diff. If that returns the DEFAULT in prod then it could be an issue with SSMS. If no rows then it is a permission, most likely `VIEW DEFINITION` – Solomon Rutzky Dec 16 '14 at 19:25
  • @srutzky the definition is different between the two environements. It's `NULL` in the production server and `getdate()` in the development server – user2023861 Dec 16 '14 at 19:32
  • 1
    That is not "different" ;-). That means you don't have permission to see it. that points to @Kritner 's answer (which should be more specific) in that you don't have permissions to `VIEW DEFINITION` for DEFAULT CONSTRAINTS, or maybe other objects as well. – Solomon Rutzky Dec 16 '14 at 19:34
  • In the question can you please address why you need to see the definition and why you currently can't? I can give you a GRANT statement to get the permission, but if you are already limited in Production then that is usually by design. And more than likely you won't be able to execute the GRANT statement if you are already that restricted. You should go talk to the DBA in charge of Production and request `VIEW DEFINITION` on the Schema or maybe `VIEW ANY DEFINITION`. They can either grant it or tell you, in a snotty tone as if you should already know, why you can't and won't so please go away. – Solomon Rutzky Dec 16 '14 at 19:50

2 Answers2

5

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.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
1

It appears the difference in viewing the object in SSMS between dev and prod is due to permission differences on your user account between dev and prod.

In order to view default values on a table object you need to have at least one of the following permissions on the object to see the default value:

ALTER on OBJECT Or CONTROL on OBJECT Or TAKE OWNERSHIP on OBJECT Or VIEW DEFINITION on OBJECT

Found this https://dba.stackexchange.com/questions/78769/minimum-sql-server-rights-that-allow-viewing-column-default-values

which seems to be pretty much answer your question :)

Community
  • 1
  • 1
Kritner
  • 13,557
  • 10
  • 46
  • 72
  • Does this mean I need to do a `Grant View Permission on to `? Also, I actually didn't know about the dba stack exchange. – user2023861 Dec 16 '14 at 19:36
  • 1
    Almost - note it's "View Definition" permission not "View Permission" see - http://technet.microsoft.com/en-us/library/ms175808(v=sql.105).aspx or any of the other permissions specified in the answer - alter, control, take ownership, or view definition – Kritner Dec 16 '14 at 19:39
  • Kritner, you might want to change that link and image. Those refer to Extended Properties. This is an issue of definition of the object itself. – Solomon Rutzky Dec 16 '14 at 19:44
  • @srutzky ah I guess you're right, having trouble finding specifics around the permissions to view a default constraint to replace it with though :O – Kritner Dec 16 '14 at 19:56
  • Updated answer to take out the extended properties link, and reference a dba question which asks something similar. – Kritner Dec 16 '14 at 20:09
  • I just looked at that link. Interesting that it _is_ view definition on the table and not the constraint! – Solomon Rutzky Dec 16 '14 at 20:12
  • 1
    @srutzky it may very well be that as well, I don't have a play ground to confirm or deny that however. Table will definitely get it though! :) – Kritner Dec 16 '14 at 20:14
  • Good to know, and I removed my comment above that said to not worry about the table ;-). – Solomon Rutzky Dec 16 '14 at 20:19