Security for SQL Server User Defined Data Types

By:   |   Updated: 2013-06-27   |   Comments (3)   |   Related: More > Data Types


I have a group of developers that I support and they are reporting they cannot see columns within their tables. I have granted them db_datareader permissions which is a standard at my company for QA environments. Why can't they see their column definitions?  Check out this tip to learn more.


The issue is the development team is taking advantage of user-defined data types, which by default is not encompassed as part of the db_datareader security permissions. Since user-defined data types are compiled code, they assume the same permissions needed by stored procedures and functions. Below is a screen shot of the example data type that I will be using for this demo.

SSMS User-Defined Data Types

The following code is what I used for this tip to create my objects that were used for testing the security of the user-defined data types.

--Create User-Defined Data Type
CREATE TYPE [dbo].[ZipCode] FROM [int] NOT NULL
--Create Table using UDDT
    CustomerID INT IDENTITY(1,1) NOT NULL,
    LastName    VARCHAR(100) NOT NULL,
    FirstName   VARCHAR(100) NOT NULL,
    ZIP         dbo.ZipCode NOT NULL
--Create Table without UDDT
CREATE TABLE Customer_Orig
    CustomerID INT IDENTITY(1,1) NOT NULL,
    LastName    VARCHAR(100) NOT NULL,
    FirstName   VARCHAR(100) NOT NULL,
    ZIP         INT NOT NULL

Next, I will create a user with just db_datareader access to the database.

--Create User with db_datareader to database
USE [master]
USE [Test]
USE [Test]
EXEC sp_addrolemember N'db_datareader', N'TestUser'

Now, I am going to log in with my new user 'TestUser' and see what I can view regarding the column information. As you can see, the table 'Customer' which uses the user-defined data type, shows no column data type information for the zip code.

SSMS No Data Type Shown

When I attempt to script out the 'Customer' table, which uses the user-defined data type, I get an error as well.

SSMS Script Error

To resolve this issue, I am going to use the GRANT VIEW DEFINITION T-SQL statement. This allows the user to see the metadata about the securable on which the permission was granted. This permission can be granted at the object level, schema level, or database wide which is the choice I am going to use.


If you wanted to secure it at the object level you could use the following statement to allow access to just that securable.


After granting this permission, you can see I can now see the column data type information, as well as the ability to script out the table.

SSMS Data Type Good

SSMS Script UDDT No Error

The VIEW DEFINITION permission is very handy when other teams, such as developers, need access to script objects and see certain metadata about stored procedures, functions, and types within SQL Server.

Next Steps

Last Updated: 2013-06-27

get scripts

next tip button

About the author
MSSQLTips author Chad Churchwell Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

View all my tips

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Friday, July 12, 2013 - 5:44:09 AM - Nitin Back To Top

Good :)

Thursday, June 27, 2013 - 5:34:13 PM - TimothyAWiseman Back To Top

This is a great tip.  I haven't encountered this problem before, and it is good to know how to deal with it and how it ties in to your permissions.

I have rarely seen a good reason to use user defined data types in SQL Server.  While good reasons certainly exist, for the most part the complications they introduce seem to outweight their benefits.

Thursday, June 27, 2013 - 8:13:44 AM - SM Back To Top


As a best practice, one may also want to consider implementing a role for such kinds of issues; for example say "db_catalogreader", and assigning the developers, e.g. (TestUser), etc. as members of the custom "db_catalogreader" role as well i.e.(rather than simply assigning each developer's, qa staff, auditor or other appropriate user account discrete access controls). Where established standards exist, one might also consider implementing a "db_catalogreader" type role in the model database on development DBMSs as a part of the standard.

Hope this helps,



Recommended Reading

Handling error converting data type varchar to numeric in SQL Server

SQL Server differences of char, nchar, varchar and nvarchar data types

How to get length of Text, NText and Image columns in SQL Server

Comparing SQL Server and Oracle datatypes

Comparison of the VARCHAR(max) and VARCHAR(n) SQL Server Data Types

get free sql tips
agree to terms

Learn more about SQL Server tools