June 27, 2013 at 2:35 am
Ugh, sorry for all the questions. I've inherited this system I know very little about, and everybody else has quit, so I'm having to try and work everything out backwards, without any documentation or history.
We have one database system (a) on which when a new record is entered, a trigger (actually several) is meant to fire and update another database (b) on the same instance of SQL Server 2008 R2 (SP2). It was all working fine until we migrated (backup & restore) the databases to new VMs. Database b is no longer being updated when a new record is inserted on database a (via the front-end).
I've run a trace, and the trigger does seem to be firing (which it should, because I checked that they weren't disabled). If the update's not happening does this suggest a permissions issue or something? What might be preventing one database interacting with another in this case (bearing in mind that they're on the same default instance).
Does anything spring to mind? Thanks.
June 27, 2013 at 4:32 am
There could be several options you should check. I'm not sure if all options I list below are required/related nor if the list is complete because I've never dealed with this myself.
If possible check these settings against the original instance/databases:
- login/user permissions in both databases and on instance level
- Cross-database Ownership Chaining
- database Trustworthy
- Allow Triggers to Fire Others (because you mentioned: several triggers)
Maybe other posters can help to add other options...
June 27, 2013 at 6:35 am
If possible check these settings against the original instance/databases:
- login/user permissions in both databases and on instance level : as far as I'm aware this is identical. I've compared everything I can think of.
- Cross-database Ownership Chaining: this is OFF on the original database instance (i.e. the one that works) and OFF on the new one too, so they are the same in that respect
- database Trustworthy: do you mean has it been corrupted? It seems to be ok.
- Allow Triggers to Fire Others (because you mentioned: several triggers): The triggers are firing when they're supposed to; the trace confirms that. It's very strange.
June 27, 2013 at 6:41 am
Do also check the actual database owner for the involved databases.
If you performed the restore, the database will have your userid as owner unless you've executed sp_changedbowner after the recovery.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 27, 2013 at 7:32 am
If the database being updated is on the same instance now, was it also on the same instance before the migration? If not, see if you're using a linked server in the old version that's no longer applicable since they're now on the same instance.
My gut tells me that this is related to permissions. If you did a backup/restore to migrate the database (which is my preferred way of doing it) then the users and their associated permissions came along with the restore. However, did the logins exist on the new server? If they're SQL logins, you need to not only check the usernames, but also that the SIDs match. The permissions may look like they're there in the new database, but if the logins are not or the SID of the user doesn't match the SID of the login, the users really don't have permission to the new database.
June 27, 2013 at 8:18 am
Thanks all.
I did sp_changedbowner after the recovery. I sorted out all the SIDS by running EXEC sp_change_users_login [etc.]. Oh, and the databases were on the same instance before as well as after.
In the end I recreated the most likely looking user and the triggers now seem to work. I still don't fully understand why but at least it's working. (Perhaps the user became corrupted?)
Thanks
June 27, 2013 at 8:40 am
Without seeing what you can query, my guess is that the SID of the user didn't match the SID of the login. If you run the following queries, the SIDs should match from user to login. The first returns the database users and the second returns the logins.
select * from dbo.sysusers;
select * from master.dbo.syslogins;
If the SIDs don't match, then the database user shown doesn't really have permission to the database. If you create SQL logins on different servers, the SIDs will be different.
Does this make any sense?
June 27, 2013 at 9:21 am
I probably explained myself badly above, sorry; the SIDS did match, that's why I was going round in circles. Thanks though.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply