Have you ever encountered an error message when trying to access the properties of a database in SQL Server? One such error is the “Could Not Use View or Function ‘msdb.dbo.sysdac_instances’ Because of Binding Errors” error. This error can be quite puzzling, especially if you are using the latest version of SQL Server Management Studio 2012.

Upon further investigation, it was discovered that SSMS 2012 automatically checks the ‘msdb.dbo.sysdac_instances’ view before opening the right-click menu of a database. This view is used to display information about DAC (Data-tier Application) instances. However, the error occurs due to binding errors in the view.

The ‘msdb.dbo.sysdac_instances’ view is created using the following SQL code:

CREATE VIEW [dbo].[sysdac_instances]
AS
    SELECT
        -- this must be locked down because we use instance_id visibility 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

Upon analyzing the code, it was discovered that the join condition in the view is based on the ‘instance_name’ column, which is retrieved from the ‘sys.databases’ table in the ‘master’ database. However, the collation of the ‘master’ database was different from the ‘msdb’ database, which caused the binding errors.

The collation of the ‘master’ database was set to ‘Latin1_General_CI_AI’, while the collation of the ‘msdb’ database was set to ‘SQL_Latin1_General_CP1_CI_AS’.

The root cause of the error was identified as the ‘msdb’ database being restored from a different server, resulting in the collation mismatch.

So, how can we resolve this issue? Unfortunately, there is no easy way to change the collation of system databases. However, we can rebuild the system databases for an instance of SQL Server using the following steps:

1. Run setup.exe with the following parameters: Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD=StrongPassword ] [ /SQLCOLLATION=CollationName]
2. Once the rebuild process is completed, restore the backups that were taken before rebuilding the system databases.

It is important to note that before running the rebuild command, you should take care of any objects that exist in the system databases, as they will be lost during the rebuild process.

By following these steps, you can resolve the binding errors and regain access to the properties of your databases in SQL Server.