microsoft

User defined types in Microsoft SQL are really handy.  Using these objects you can construct queries like

select duration.minutes from cdrs;
select duration.seconds from cdrs;

You can also program them to read weird string inputs that other data types can not handle.  Recently I wrote a custom data type to read a duration field of the format minutes:seconds.tenths; so standard input went something like ’002:34.2.’

Writing a custom data type is actually very simple.  You just open a new sql data type project in Visual Studio and VS will integrate nicely with your sql server; even going so far as to upload the data type for you and run tests.  There are numerous tutorials online for programming a data type however so I will not cover that aspect.

Like I said I recently created a data type for my data parsing.  I put it into my table but soon found that assigning my specific data processing user permissions to the object was harder than you would expect.  I kept getting the error

[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'data_type', database 'db', schema 'dbo'. (SQL-42000)

Trying

grant execute on [data_type] to cdr;

returned ‘object not found.’  Ms sql’s security assignment tool did not help either, it never did ‘find’ the object I created.

I eventually found my way to this msdn page which at least revealed the secret missing keyword.  To set the permission on the object use this query

grant execute on TYPE::[dbo].[data_type] to cdr;