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.