7

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?

3
  • I think you need ALTER permissions on that table. Try granting that user ALTER permissions and see if that helps. Oct 9, 2014 at 13:24
  • @Kris no, you shouldn't grant ALTER to view definitions, unless you also want them to be able to ALTER. For just viewing definitions, that's what VIEW DEFINITION is for. Oct 9, 2014 at 13:40
  • Ahh, my mistake, i misread the question. I thought they needed to view and change them. My apologies. Oct 9, 2014 at 13:42

1 Answer 1

9

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;
8
  • 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];. Mar 2, 2015 at 15:07
  • @Simon See updated answer. Mar 2, 2015 at 15:16
  • Thanks Aaron. I thought maybe there was a way to do this for whole tables all at once without executing something on each table one by one like that. But it works for me so I go with this. Big thank to you. Mar 2, 2015 at 15:20
  • 1
    @Simon Sure, you could grant view definition on the schema (e.g. dbo, GRANT VIEW DEFINITION ON SCHEMA::dbo TO [username];). However there is no way to restrict that to just the definition of tables. Mar 2, 2015 at 15:22
  • 1
    @Simon mssqltips.com/sqlservertip/3185/… Mar 2, 2015 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.