Consideration when applying FIM Database backups (DB Owner SID) RRS feed

  • Question

  • Hello,

    we experienced an obscure issue in FIM, that costs us a lot of time and effort.

    I would like to share my experiences.

    We recently applied a FIMService Database backup for our FIM environment, due to a failed change implementation.

    After implementing the fallback Database, everything was fine at the first glance. After some weeks, I recognized that our criteria based groups and sets in the FIM portal with date time filters doesn’t work properly as intended and the necessary MPR’s didn’t get fired.

    Long story short, I figured out that the SQL Server Agent Jobs didn’t run successful since our fallback. In our case the FIM_TemporalEventsJob (This job evaluates temporal sets and policies, validates set and group membership, and runs daily by default.)

    Recognized failure in the Job history:

    'EXECUTE AS LOGIN' failed for the requested login “ServiceAccount”.  The step failed.

    That was strange, because the ServiceAccount, that runs the Jobs is owner of the FIMService Database.

    Further investigations reveals following:

    The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XXXX'. You should correct this situation by resetting the owner of database 'XXXX' using the ALTER AUTHORIZATION statement.

    To prove that the problem is in fact differing SID's I ran the following two SQL statements.

    • To get owner SID recorded in the master database for the current database
      SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()
    •  To get the owner SID recorded for the current database owner
      SELECT sid FROM sys.database_principals WHERE name=N'dbo'

    They should return you SID values in the format of a GUID

    Now if the two SID's differ which they did in my case it means that you need to reset the database owner so that both values are the same. To do this you can run another ALTER statement and pass in the owner value you want to use e.g

     ALTER AUTHORIZATION ON Database::XXXX TO [domain\user]

    Once I had run this code the problem was fixed.

    Hope this helps for those for those, having similar issues.

    Thanks Fatih

    Wednesday, May 13, 2015 12:00 PM

All replies

  • Faith,

    Thanks for sharing. This tends to occur when databases are backed up on a server in one domain and restored in another or if the database owner had changed in between the back up and restore. Did either of these occur?

    David Lundell, Get your copy of FIM Best Practices Volume 1

    Friday, July 17, 2015 12:19 AM