SQL Server User-Defined Data Types Not Visible to MS Access on Linked Table Refresh
Had one of those, "am I on crazy pills?!" problems the past few weeks. In an MS Access database I had a whole slew of SQL Server Linked Tables that included User-Defined Data Types(UDT). The UDTs were mostly auditing fields that referenced defaults to pull in the system.user and current datetime.
Everything worked great.
That was until I added a field and asked a user (with very elevated privileges) to refresh the linked tables. They did, but the audit fields referencing the user-defined types disappeared.
At first, I thought it was some silliness in their file-based DSN, but even using my connection (with Windows-based security... so it pointed to their account) the audit fields remained hidden.
To make the whole story more perverse, if I used the same connection logged in as me (I am a dbo) the tables refreshed with the audit fields and they could also see them.
After much googling I finally stumbled upon this post and this msdn article which indicate that user-defined types have permissions including: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION and REVOKE.
To set permissions to allow anyone to view a user-defined datatype, use the following
grant execute on TYPE::[dbo].[user_data_type] to public;
Or the appropriate user or role.
What is this?
We use the IFWIS Blog to keep our staff, cooperators and the public updated on what we are working on, what's new and share tips about using our software and website.
The content of the IFWIS Blog is often highly technical, but we try our best to keep our posts accessible and jargon-free. When this is not possible, we'll link to other resources online to provide background. Thanks for reading and please follow our blog for updates.
- IFWIS RSS Feed
- Daily Email Digest (All Public Content)
- Or follow just one of the many topic-specific tag feeds or pages below.
- 1 of 6