This error is one of an interesting which appeared as soon as we right click on the database. It says Could Not Use View or Function ‘msdb.dbo.sysdac_instances’ Because of Binding Errors.
Due to this error, we were not able to see the properties of the database. Interesting this was happening only when we use the latest version of SQL Server Management Studio 2012. Which made me think that SSMS 2012 automatically checks this view before opening the ‘right click’ menu of a database.
We captured profile and found that the error was raised while using this view.
CREATE VIEW [dbo].[sysdac_instances] AS SELECT -- this must be locked down because we use instance_id visability as a security gate case when (dbo.fn_sysdac_is_currentuser_sa() = 1) then dac_instances.instance_id when sd.owner_sid = SUSER_SID() then dac_instances.instance_id else NULL end as instance_id, dac_instances.instance_name, dac_instances.type_name, dac_instances.type_version, dac_instances.description, case when (dbo.fn_sysdac_is_currentuser_sa() = 1) then dac_instances.type_stream when sd.owner_sid = SUSER_SID() then dac_instances.type_stream else NULL end as type_stream, dac_instances.date_created, dac_instances.created_by, dac_instances.instance_name as database_name FROM sysdac_instances_internal dac_instances LEFT JOIN sys.databases sd ON dac_instances.instance_name = sd.name
As per the above View we see join on “ON dac_instances.instance_name = sd.name”. Here sys.databases are coming from MASTER database and sysdac_instances is from MSDB database. We have queried sys.database and found the collation was different as seen below.
Now we know the cause of the error. Later we identified that MSDB was restored from a different server, which caused this issue.
There is no easy way to change collation of system databases. To rebuild the system databases for an instance of SQL Server we need to run setup.exe with below parameters.
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
Once completed, we restored the backups which we have taken before rebuilding system databases. We should take care of objects which are there in system databases before running rebuild command.
Reference: Pinal Dave (https://blog.sqlauthority.com)