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