Security for SQL Server User Defined Data Types

By:   |   Comments (3)   |   Related: > 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.

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
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.

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.

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.

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




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

Good :)


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

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 (25595)

Hi,

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,

SM















get free sql tips
agree to terms