Updating of tables ends up with a messy error message about incompatible type

After modifying one of tables in C.Net I ended up with an error message saying “Incompatible type blah-blah-blah” on table save (sorry, don’t have exact words or screenshot). Turned out that I have changed type of one of the fields (from computed Text to Pick-List). This drove mad the security permission model and HR.Net could not re-generate the security views it uses in the background.

To figure out what exact permission was causing issues I came up with this script:

from sys.objects o
    join sys.sql_modules m on m.object_id = o.object_id
    cross join sys.views vs 
where o.object_id = object_id(vs.name)
  and o.type = 'V'
  and vs.name like '%[_]tablename[_]%'

This script outputs a list of all views generated by the system and outputs actual SQL that is behind the view. That way I could easily identify what role and what permission was causing the issue and could modify.

I was lucky I remembered the table name I modified, otherwise I would have to look through a gazillion of SQL Views and figure out which one is causing issues to fragile internals of HR.Net

This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *