CODEWITHSUNDEEP
sql-serverpermissionsuser-defined-types
Brondahl
Brondahl

Reputation: 8557

SQL - Permissions on an User-Defined Alias Type

As (approximately) the project DBA, I've created a type alias:

CREATE TYPE StockQuantity AS numeric(18,2)

I've then used that in views and tables and so forth.

Some of the other developers, who have slightly lower db permissions (project politics) now can't update those views and permissions.

When they try to, they get:

Cannot find the type 'StockQuantity', because it does not exist or you do not have permission.

If they replace StockQuantity with numeric(18,2), it works fine. If I run the ALTER script, it works fine.

Evidently SQL server regards Alias Type as being something that requires permissions. Ugh.

What do I need to change to fix this? The only search results I could find talk about GRANT-ing EXECUTE permissions on User-Defined Table Types, which doesn't seem relevant here?

Upvotes: 3

Views: 2108

Answers (1)

Brondahl
Brondahl

Reputation: 8557

Answer - I needed to GRANT the REFERENCES permission for other users.

GRANT REFERENCES ON TYPE::dbo.StockQuantity TO OtherDevUser;

By default users had EXECUTE and VIEW DEFINITION. I assigned REFERENCES and stuff worked again. Other permissions available are TAKE OWNERSHIP and CONTROL

MSDN citation that that this is indeed necessary for Created Types: https://msdn.microsoft.com/en-us/library/ms175007.aspx

Unlike user-defined types that are created by using sp_addtype, the public database role is not automatically granted REFERENCES permission on types that are created by using CREATE TYPE. This permission must be granted separately.

I solved this by finding a Broader discussion of the issue here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/274456c2-1e4a-436b-b10b-a972e95f9e87/references-and-view-definition-permissions-to-a-user-on-an-alias-data-type?forum=sqlsecurity

To establish what user others were acting I got him to run:

SELECT CURRENT_USER

To verify what permissions I and the other Dev had I ran:

SELECT * FROM fn_my_permissions('StockQuantity','type')

Upvotes: 1

Related Questions