July 28, 2009 at 10:45 am
I am tasked with ensuring that User Ids saved in application databases (for example in a ‘CreatedBy’ field) are valid user ids in the “authentication” database, which is where the User Ids are generated.
Since foreign keys can’t cross databases, it has been suggested to try a constraint against a view. Does anyone have input on this or alternate solutions?
July 28, 2009 at 1:28 pm
Validation of userid should be done at login time, not at the time application is inserting/updating every single row -such a solution would add a big deal of overhead, for no good reason.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 28, 2009 at 1:33 pm
Agreed, and user validation is done at login time. However we have UserIds assigned to things other than standard users (ie utilities, importing tools) and the UserId values are sometimes plugged by stored procs or processes other than the user-run app. We want to ensure that bogus UserId values aren't being used.
July 28, 2009 at 1:54 pm
I see. Solution would be a check (domain) constraint pointing to a linked server, you can find documentation here http://msdn.microsoft.com/en-us/library/ms188258.aspx
Disclaimer.
I wouldn't do it. This solution would force SQL Server to execute the check function and query the target table in the target linked server once per each insert/update.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 28, 2009 at 2:06 pm
I'd actually run a maintenance job to clean these up every night, or flag them for someone to look at. Rather than do this at insert time, which could cause issues.
Or have the app make a check before sending the insert.
July 28, 2009 at 2:13 pm
[font="Verdana"]From a database architecture point of view, I would have to ask one question: why are the applications on different servers?
If they are on separate servers for a reason, it's likely that linking them using some sort of tight constraint wouldn't be such a great idea. If they're not on separate servers for a reason, then perhaps you can look at server consolidation?
In the mean time, I suggest you consider some form of transactional replication for the users table.
[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply