SQL Server – Minimum Rights to View Column Default Values

sql serversql-server-2008

I have an SQL Server 2008 database, some of the fields (columns) in some of the tables are configured with default values.

I have a requirement to limit user security as much as possible, ideally only allow read/write permissions. However, the users need to be able to see what the default values for the columns (well it's actually an application that needs to read the defaults).

Normally I can give the user access to the following roles if I want read/write only:

  • db_datareader
  • db_datawriter

The problem is that this prevents access to the default values. This can be confirmed by opening a table is design view (they get a message saying they have limited access and can only view the table design), and the default values are just shown blank.

If I add the db_owner role then they can view the default values fine, but I don't want to give the owner powers.

Is there something else I can do in order to restrict to only read/write but with the extra ability to to see what the default values are?

Best Answer

This will allow the defaults to show up in the lower pane of the table designer:

GRANT VIEW DEFINITION ON dbo.tablename TO [username];

Of course this doesn't give them the ability to make any changes to the table, and in general I discourage the use of the designers anyway. They're littered with bugs and inconsistencies. With VIEW DEFINITION rights, your user can also pull this information from the catalog views:

SELECT 
  [constraint] = d.name, 
  [column] = c.name, 
  [default] = d.definition 
FROM sys.default_constraints AS d
  INNER JOIN sys.tables AS t
  ON d.parent_object_id = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON s.[schema_id] = t.[schema_id]
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  AND d.parent_column_id = c.column_id
  WHERE s.name = N'dbo'
    AND t.name = N'tablename';

Is there a way to do something similar for whole tables of database? I mean, about the GRANT VIEW DEFINITION ON dbo.tablename TO [username];.

Sure, dynamic SQL is very handy here.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N''GRANT VIEW DEFINITION ON ' 
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
  + N' TO [username];' + CHAR(13) + CHAR(10)
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id];

PRINT @sql;
-- EXEC sys.sp_executesql @sql;
Related Question