Grant VIEW DEFINITION permission to tables and/or views

SQL Server 2005 still supports all of the fixed server and database roles introduced in SQL Server 2000, along with their default permissions. However, custom database roles, application roles, as well as server logins and database users can be granted (or denied) more granular permissions on a much wider range of objects. Some of the most commonly used permission types present in earlier version of SQL Server, such as EXECUTE, SELECT, or TAKE OWNERSHIP offer new possibilities in their new implementation (since they can be applied on different levels and offer inheritance capabilities). There are also others, newly introduced ones, including, for example:

• CONTROL - functionally equivalent to all permissions granted to the object's owner and inherited by all subentities within its scope,
• ALTER - provides the ability to alter properties of an object. Depending on the scope, you can limit its inheritance to objects of a specific type (for example, its variation in the form ALTER ANY 'object_type' grants permissions to modify every instance of 'object_type' within server or database scope). ALTER TRACE allows running Profiler without membership in SysAdmin fixed server role.
• IMPERSONATE - as mentioned earlier, permits impersonating another user (without requiring SysAdmin or dbo privileges, as was the case in SQL Server 2000),
• and VIEW DEFINITION

According to BOL about VIEW DEFINITION Permission:

The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.

And now the reasons for this blog entry: If you are using MS Access as your front end for SQL Server 2005, and you have tables with not-nullable columns with default values, as for instance:

CREATE TABLE [dbo].[MyTable] (
  [Id] [int] NOT NULL,
  ... more columns...
  [ColumnName] [int] NOT NULL CONSTRAINT [DF_TMyTable_ColumnName] DEFAULT ((0))
  CONSTRAINT [PK_TMyTable] PRIMARY KEY NONCLUSTERED
  ( [Id] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

your MS Access application will not be aware of the default values for columns in the table and your insertions will fail unless the client manually sets a value for those default values. According, to BOL on Viewing Object Definitions, this is by design:

If the user is not the table owner of myTable and has been granted only SELECT permission on the table, when the user accesses the following catalog views, the definition columns are NULL.

Catalog view name	Transact-SQL definitions for
sys.sql_modules	All triggers in myTable
sys.computed_columns	All computed column in myTable
sys.check_constraints	All CHECK constraints in myTable
sys.default_constraints	All DEFAULT constraints in myTable

If you, your application or your users were used to work with SQL Server 2000 this will appear as a new behaviour, since granting SELECT access to a table in SQL Server 2000 also granted to read the structure of the table itself. Now, in SQL Server 2005, this is a different right and has to be granted explicitly. In fact it is the least privilege: you can only read the structure of the object (securable) but nothing else, you cannot even read data (SELECT). To solve the problem with MS Access not inserting the default values for tables and views, you simply have to grant users the right to VIEW DEFINITION of the tables/views. Keywords: SQL Server 2005, identity, not null, not nullable, default, default value, constraint, view definition, ms access, problem, issue, insert, select, permission.