db_datareader can't see user-defined types

I have a very simple SQL Server 2005 database, nothing fancy except that I use user-defined types (e.g., udt_Name = varchar(20)). I just set up a login for my first user, using Windows authentication, and gave her the following database access:

default schema = dbo

role = db_datareader

role = db_datawriter

Using SQL Server Management Studio, she can open any table; however, every column that is defined with a user-defined type appears with generic column headings (Expr1, Expr2 etc). And when she opens a table in design view, all columns with user-defined types have their Data Type field listed as "invalid type" rather than, say "udt_Name:varchar(20)". I browsed to the "User-defined Data Types" entry in Object Explorer, and it was empty.

In other words, it appears that she is unable to access the user-defined type aliases, even though she has read/write access to the database. As a very temporary work-around, I gave her membership in the database role "db_owner", but this is obviously not an ideal solution.

User-defined types are a great organizing tool and I don't want to have to redefine all my tables without them. What are my options?

I am not an expert on the management tools, but my guess is that most likely the reason is that db_datareader and db_datawriter roles don’t have any permission on the data type by default and therefore it fails when trying to query information about the data type.

I would suggest trying to grant VIEW DEFINITION permission to the user-defined role, for example:

GRANT VIEW DEFINITION ON TYPE::[udt_name] TO [role_name]

This will grant full access to the metadata for udt_name. You would need to do this operation for each data type.

As an alternative, and if in your security model protecting metadata on the database against this role is not a concern, you can simplify the above step for all objects in the DB:

GRANT VIEW DEFINITION TO [role_name]

I hope this information will be useful. If you still have any trouble let us know and we will try to help.

-Raul Garcia

SDE/T

SQL Server Engine