Security for SQL Server User Defined Data Types

-- Security for SQL Server User Defined 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.*/

-- Create User-Defined Data Types

USE Testing

CREATE TYPE [dbo].[ZipCode] FROM [int] NOT NULL

GO

-- Create Table Using User-Defined Data Types

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 User-Defined Data Types

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

--Create User with db_datareader to database

USE [master]

GO

CREATE LOGIN [Testing] WITH PASSWORD=N'TestUser',

 DEFAULT_DATABASE=[master],

 CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [Testing]

GO

CREATE USER [TestUser] FOR LOGIN [Testing]

GO

USE [Testing]

GO

EXEC sp_addrolemember N'db_datareader', N'Testing'

GO

/*

Now, I am going to log in with my new user 'Testing' 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.

*/

/*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