SQL Server

Error in SSMS when expanding Views folder in database created using DBCC CLONEDATABASE

SQL Server allows us to create database clone using DBCC CLONEDATABASE command. Cloned database can be then used in SQL Server Management Studio like any other database. But for sure exception exists there: When we will try to browse object contained in cloned database everything works (procedures, functions, ..) until we will expand Views folder. Then SSMS will throw following exception:

There is the query executed by SSMS in the background:

exec sp_executesql N'SELECT
v.name AS [Name],
SCHEMA_NAME(v.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
        serverproperty(N''Servername'')
       AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/View[@Name='' + quotename(v.name,'''''''') 
	   + '' and @Schema='' + quotename(SCHEMA_NAME(v.schema_id),'''''''') + '']'' AS [Urn],
v.create_date AS [CreateDate],
ISNULL(sv.name, N'''') AS [Owner],
CAST(CASE WHEN ISNULL(smv.definition, ssmv.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CAST(ISNULL(OBJECTPROPERTYEX(v.object_id, N''IsSchemaBound''),0) AS bit) AS [IsSchemaBound],
CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusteredIndex]
FROM
sys.all_views AS v
LEFT OUTER JOIN sys.database_principals AS sv ON sv.principal_id = ISNULL(v.principal_id, (OBJECTPROPERTY(v.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmv ON ssmv.object_id = v.object_id
LEFT OUTER JOIN sys.indexes AS idx ON idx.object_id = v.object_id and (idx.index_id < @_msparam_0)
WHERE
(v.type = @_msparam_1)and(CAST(
 case 
    when v.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = v.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N''microsoft_database_tools_support'') 
        is not null then 1
    else 0
end          
             AS bit)=@_msparam_2)
ORDER BY
[Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',
@_msparam_0=N'2',@_msparam_1=N'V',@_msparam_2=N'0'

If we will try to execute this query directly from SSMS we will see this error message:

After deeper investigation I have found following single statement is causing the exception:

SELECT OBJECTPROPERTY(v.object_id, 'OwnerId')
FROM sys.all_views AS v

When calling the OBJECTPROPERTY function with ‘OwnerId’ as a parameter exception is fired and connection closed. Thankfully this is happening only when querying cloned database and not standard one.

I thought that it can be somehow related to security and SIDs matching in cloned database.

Error was reported to Microsoft Azure Feedback.

Leave a Reply

Your email address will not be published. Required fields are marked *