Skip to main content

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.




References:
Labels:

Comments

Post a Comment

Popular posts from this blog

ArcGIS One-to-Many Labeling

ArcGIS is just plain lousy at dealing with any relationship that isn't one-to-one. We all have a slew of hacks just to deal with this limitation. I for one regularly am creating temporary cross-tab queries so I can represent multiple sample results at a collection point, fish surveyed at a cross-section and a host of other relationships. The classic example for mapping comes from the cadastral community and condominium lots. It's an odd situation where more than one person has title to the same piece of ground. How do you represent this? I've got a new trick thanks to Mohammed Hoque's article in ArcUser Magazine. We're going to do a database query inside a label expression, loop through the results and output the entire list to label. For our example we'll use Outfitting Areas in Idaho and we'll label them with the Outfitters and Guide License Numbers and Outfitter Names. 1.) Open ArcGIS and add your spatial layer with the unique identifier shared with...
15 comments
Read more

Simple HTTP Redirect with Querystring in IIS7

HTTP Redirect seems simple enough. Always was in IIS6 and in IIS7 there's even a button labeled HTTP Redirect that promises relative redirects.  It looks like it'll be as easy Apache finally.  That is until you try to redirect a querystring.  Then everything bombs. Turns out it still is relatively easy, except you have to know that Microsoft changed $S$Q to $V$Q. Why? $Ss and $Gs I suspect. And How. In our example we'll redirect all pages under http://olddomain.com/content to http://mydomain.com/content. Pick the virtual directory you want to redirect. e.g. http://olddomain.com/content Click HTTP Redirect under IIS in the IIS management console. In the HTTP Redirect Dialog: Check Redirect requests to this destination Enter your new path ending with $V$Q.  e.g. http://mydomain.com$V$Q Counter-intuitively check Redirect all request to exact destination (instead of relative destination) Choose the appropriate Status Code (Permanent or Temp...
5 comments
Read more

Simple Record-Level Auditing in SQL Server & ArcSDE

I've had simple auditing set up in my databases for years thanks to our old DBA. This is not robust transaction logging, it just answers the questions "Who created this?", "When was this created?", "Who last edited this?" and "When was this last edited?". After witnessing a brand new table audit itself without triggers, I set about to understand the setup. This seemed curious to me, so I thought it might also be curious to you. It turned out to be a two step system which rendered my insert triggers superfluous (at least I think so, tell me if I'm missing something and I need insert triggers after all). Our solution uses a combination of two methods. For inserts we are going to tie our audit fields to User Defined Data Types which inherit Defaults we have specified. For updates we will build a trigger to log the changes. We will use the following fields in our tables to track inserts and updates: AUD_NUser - Creator AUD_NDate - Creati...
4 comments
Read more