Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Security for SQL Server User Defined Data Types


By:   |   Updated: 2013-06-27   |   Comments (3)   |   Related: More > 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


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




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 (*).

*Name    *Email    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

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


Learn more about SQL Server tools