Security for SQL Server User Defined Data Types

Problem

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.

Solution

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.

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
GO
--Create Table using UDDT
CREATE TABLE Customer
(
    CustomerID INT IDENTITY(1,1) NOT NULL,
    LastName    VARCHAR(100) NOT NULL,
    FirstName   VARCHAR(100) NOT NULL,
    ZIP         dbo.ZipCode NOT NULL
)
GO
--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
)
GO

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

--Create User with db_datareader to database
USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'TestUser', 
 DEFAULT_DATABASE=[master], 
 CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Test]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
USE [Test]
GO
EXEC sp_addrolemember N'db_datareader', N'TestUser'
GO

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.

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

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.

GRANT VIEW DEFINITION TO TestUser;

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

GRANT VIEW DEFINITION ON TYPE::dbo.ZipCode TO TestUser

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.

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.

Source Collected from MSSQLTIPS.COM