背景介绍

    SQL Server 关系图是一个非常简单易用且方便的工具，可以直观的把数据库中表之间的关系展现出来，不用手动整理或者写脚本整理各个表之间的关系，如下图。 


    RDS SQL Server 2008 R2由于权限的限制，默认是不支持创建关系图的。但是，近期遇到一些客户，同一个实例中，同一个账号，在两个数据库DB1和DB2都是读写权限，但是DB1上面可以创建关系图， DB2上面却不可以。而且这种情况，在众多其他的RDS 2008 R2实例中，很难复现。

    针对上述问题，我们做了一番研究。

问题描述

    RDS SQL Server 2008 R2，连接实例， 右击数据库关系图，选择“新建数据库关系图”选项，出现提示，缺少一个或者多个支持对象

点“是”，创建对象，接下来出现错误

“hattytest”账号在添加对象的时候，报错没有权限。

排查分析

RDS SQL Server 2008 R2 新建的数据库，默认是没有关系图所需的支撑对象的，需要创建数据库关系图时，会提示缺少一个或者多个支持的对象，即下面这些对象

Sysdiagrams table
sp_alterdiagam stored procedure
sp_creatediagram stored procedure
sp_dropdiagram stored procedure
sp_renamediagram stored procedure
fn_diagramobjects function
sp_helpdiagrams stored procedure
sp_helpdiagramsdefinition stored procedure
sp_upgraddiagrams stored procedure

这些对象的定义如下

IF OBJECT_ID(N'dbo.sp_upgraddiagrams') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
	EXEC sp_executesql N'
	CREATE PROCEDURE dbo.sp_upgraddiagrams
	AS
	BEGIN
		IF OBJECT_ID(N''dbo.sysdiagrams'') IS NOT NULL
			return 0;
	
		CREATE TABLE dbo.sysdiagrams
		(
			name sysname NOT NULL,
			principal_id int NOT NULL,	-- we may change it to varbinary(85)
			diagram_id int PRIMARY KEY IDENTITY,
			version int,
	
			definition varbinary(max)
			CONSTRAINT UK_principal_name UNIQUE
			(
				principal_id,
				name
			)
		);


		/* Add this if we need to have some form of extended properties for diagrams */
		/*
		IF OBJECT_ID(N''dbo.sysdiagram_properties'') IS NULL
		BEGIN
			CREATE TABLE dbo.sysdiagram_properties
			(
				diagram_id int,
				name sysname,
				value varbinary(max) NOT NULL
			)
		END
		*/

		IF OBJECT_ID(N''dbo.dtproperties'') IS NOT NULL
		begin
			insert into dbo.sysdiagrams
			(
				[name],
				[principal_id],
				[version],
				[definition]
			)
			select	 
				convert(sysname, dgnm.[uvalue]),
				DATABASE_PRINCIPAL_ID(N''dbo''),			-- will change to the sid of sa
				0,							-- zero for old format, dgdef.[version],
				dgdef.[lvalue]
			from dbo.[dtproperties] dgnm
				inner join dbo.[dtproperties] dggd on dggd.[property] = ''DtgSchemaGUID'' and dggd.[objectid] = dgnm.[objectid]	
				inner join dbo.[dtproperties] dgdef on dgdef.[property] = ''DtgSchemaDATA'' and dgdef.[objectid] = dgnm.[objectid]
				
			where dgnm.[property] = ''DtgSchemaNAME'' and dggd.[uvalue] like N''_EA3E6268-D998-11CE-9454-00AA00A3F36E_'' 
			return 2;
		end
		return 1;
	END
	'

END

-- This sproc could be executed by any other users than dbo
IF IS_MEMBER('db_owner') = 1
	EXEC dbo.sp_upgraddiagrams;

IF OBJECT_ID(N'dbo.sp_helpdiagrams') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
	EXEC sp_executesql N'
	CREATE PROCEDURE dbo.sp_helpdiagrams
	(
		@diagramname sysname = NULL,
		@owner_id int = NULL
	)
	WITH EXECUTE AS N''dbo''
	AS
	BEGIN
		DECLARE @user sysname
		DECLARE @dboLogin bit
		EXECUTE AS CALLER;
			SET @user = USER_NAME();
			SET @dboLogin = CONVERT(bit,IS_MEMBER(''db_owner''));
		REVERT;
		SELECT
			[Database] = DB_NAME(),
			[Name] = name,
			[ID] = diagram_id,
			[Owner] = USER_NAME(principal_id),
			[OwnerID] = principal_id
		FROM
			sysdiagrams
		WHERE
			(@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
			(@diagramname IS NULL OR name = @diagramname) AND
			(@owner_id IS NULL OR principal_id = @owner_id)
		ORDER BY
			4, 5, 1
	END
	'


	GRANT EXECUTE ON dbo.sp_helpdiagrams TO public
	DENY EXECUTE ON dbo.sp_helpdiagrams TO guest
END

IF OBJECT_ID(N'dbo.sp_helpdiagramdefinition') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
	EXEC sp_executesql N'
	CREATE PROCEDURE dbo.sp_helpdiagramdefinition
	(
		@diagramname 	sysname,
		@owner_id	int	= null 		
	)
	WITH EXECUTE AS N''dbo''
	AS
	BEGIN
		set nocount on

		declare @theId 		int
		declare @IsDbo 		int
		declare @DiagId		int
		declare @UIDFound	int
	
		if(@diagramname is null)
		begin
			RAISERROR (N''E_INVALIDARG'', 16, 1);
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N''db_owner'');
		if(@owner_id is null)
			select @owner_id = @theId;
		revert; 
	
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname;
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId ))
		begin
			RAISERROR (''Diagram does not exist or you do not have permission.'', 16, 1);
			return -3
		end

		select version, definition FROM dbo.sysdiagrams where diagram_id = @DiagId ; 
		return 0
	END
	'


	GRANT EXECUTE ON dbo.sp_helpdiagramdefinition TO public
	DENY EXECUTE ON dbo.sp_helpdiagramdefinition TO guest
END

IF OBJECT_ID(N'dbo.sp_creatediagram') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
	EXEC sp_executesql N'
	CREATE PROCEDURE dbo.sp_creatediagram
	(
		@diagramname 	sysname,
		@owner_id		int	= null, 	
		@version 		int,
		@definition 	varbinary(max)
	)
	WITH EXECUTE AS ''dbo''
	AS
	BEGIN
		set nocount on
	
		declare @theId int
		declare @retval int
		declare @IsDbo	int
		declare @userName sysname
		if(@version is null or @diagramname is null)
		begin
			RAISERROR (N''E_INVALIDARG'', 16, 1);
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID(); 
		select @IsDbo = IS_MEMBER(N''db_owner'');
		revert; 
		
		if @owner_id is null
		begin
			select @owner_id = @theId;
		end
		else
		begin
			if @theId <> @owner_id
			begin
				if @IsDbo = 0
				begin
					RAISERROR (N''E_INVALIDARG'', 16, 1);
					return -1
				end
				select @theId = @owner_id
			end
		end
		-- next 2 line only for test, will be removed after define name unique
		if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname)
		begin
			RAISERROR (''The name is already used.'', 16, 1);
			return -2
		end
	
		insert into dbo.sysdiagrams(name, principal_id , version, definition)
				VALUES(@diagramname, @theId, @version, @definition) ;
		
		select @retval = @@IDENTITY 
		return @retval
	END
	'


	GRANT EXECUTE ON dbo.sp_creatediagram TO public
	DENY EXECUTE ON dbo.sp_creatediagram TO guest
END

IF OBJECT_ID(N'dbo.sp_renamediagram') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
	EXEC sp_executesql N'
	CREATE PROCEDURE dbo.sp_renamediagram
	(
		@diagramname 		sysname,
		@owner_id		int	= null,
		@new_diagramname	sysname
	
	)
	WITH EXECUTE AS ''dbo''
	AS
	BEGIN
		set nocount on
		declare @theId 			int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
		declare @DiagIdTarg		int
		declare @u_name			sysname
		if((@diagramname is null) or (@new_diagramname is null))
		begin
			RAISERROR (''Invalid value'', 16, 1);
			return -1
		end
	
		EXECUTE AS CALLER;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N''db_owner''); 
		if(@owner_id is null)
			select @owner_id = @theId;
		REVERT;
	
		select @u_name = USER_NAME(@owner_id)
	
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
		begin
			RAISERROR (''Diagram does not exist or you do not have permission.'', 16, 1)
			return -3
		end
	
		-- if((@u_name is not null) and (@new_diagramname = @diagramname))	-- nothing will change
		--	return 0;
	
		if(@u_name is null)
			select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @new_diagramname
		else
			select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @owner_id and name = @new_diagramname
	
		if((@DiagIdTarg is not null) and  @DiagId <> @DiagIdTarg)
		begin
			RAISERROR (''The name is already used.'', 16, 1);
			return -2
		end		
	
		if(@u_name is null)
			update dbo.sysdiagrams set [name] = @new_diagramname, principal_id = @theId where diagram_id = @DiagId
		else
			update dbo.sysdiagrams set [name] = @new_diagramname where diagram_id = @DiagId
		return 0
	END
	'


	GRANT EXECUTE ON dbo.sp_renamediagram TO public
	DENY EXECUTE ON dbo.sp_renamediagram TO guest
END

IF OBJECT_ID(N'dbo.sp_alterdiagram') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
	EXEC sp_executesql N'
	CREATE PROCEDURE dbo.sp_alterdiagram
	(
		@diagramname 	sysname,
		@owner_id	int	= null,
		@version 	int,
		@definition 	varbinary(max)
	)
	WITH EXECUTE AS ''dbo''
	AS
	BEGIN
		set nocount on
	
		declare @theId 			int
		declare @retval 		int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
		declare @ShouldChangeUID	int
	
		if(@diagramname is null)
		begin
			RAISERROR (''Invalid ARG'', 16, 1)
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID();	 
		select @IsDbo = IS_MEMBER(N''db_owner''); 
		if(@owner_id is null)
			select @owner_id = @theId;
		revert;
	
		select @ShouldChangeUID = 0
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		
		if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound))
		begin
			RAISERROR (''Diagram does not exist or you do not have permission.'', 16, 1);
			return -3
		end
	
		if(@IsDbo <> 0)
		begin
			if(@UIDFound is null or USER_NAME(@UIDFound) is null) -- invalid principal_id
			begin
				select @ShouldChangeUID = 1 ;
			end
		end

		-- update dds data			
		update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ;

		-- change owner
		if(@ShouldChangeUID = 1)
			update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ;

		-- update dds version
		if(@version is not null)
			update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ;

		return 0
	END
	'


	GRANT EXECUTE ON dbo.sp_alterdiagram TO public
	DENY EXECUTE ON dbo.sp_alterdiagram TO guest
END

IF OBJECT_ID(N'dbo.sp_dropdiagram') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
	EXEC sp_executesql N'
	CREATE PROCEDURE dbo.sp_dropdiagram
	(
		@diagramname 	sysname,
		@owner_id	int	= null
	)
	WITH EXECUTE AS ''dbo''
	AS
	BEGIN
		set nocount on
		declare @theId 			int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
	
		if(@diagramname is null)
		begin
			RAISERROR (''Invalid value'', 16, 1);
			return -1
		end
	
		EXECUTE AS CALLER;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N''db_owner''); 
		if(@owner_id is null)
			select @owner_id = @theId;
		REVERT; 
		
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
		begin
			RAISERROR (''Diagram does not exist or you do not have permission.'', 16, 1)
			return -3
		end
	
		delete from dbo.sysdiagrams where diagram_id = @DiagId;
	
		return 0;
	END
	'


	GRANT EXECUTE ON dbo.sp_dropdiagram TO public
	DENY EXECUTE ON dbo.sp_dropdiagram TO guest
END

IF OBJECT_ID(N'dbo.fn_diagramobjects') IS NULL and IS_MEMBER('db_owner') = 1
BEGIN
	EXEC sp_executesql N'
	CREATE FUNCTION dbo.fn_diagramobjects() 
	RETURNS int
	WITH EXECUTE AS N''dbo''
	AS
	BEGIN
		declare @id_upgraddiagrams		int
		declare @id_sysdiagrams			int
		declare @id_helpdiagrams		int
		declare @id_helpdiagramdefinition	int
		declare @id_creatediagram	int
		declare @id_renamediagram	int
		declare @id_alterdiagram 	int 
		declare @id_dropdiagram		int
		declare @InstalledObjects	int

		select @InstalledObjects = 0

		select 	@id_upgraddiagrams = object_id(N''dbo.sp_upgraddiagrams''),
			@id_sysdiagrams = object_id(N''dbo.sysdiagrams''),
			@id_helpdiagrams = object_id(N''dbo.sp_helpdiagrams''),
			@id_helpdiagramdefinition = object_id(N''dbo.sp_helpdiagramdefinition''),
			@id_creatediagram = object_id(N''dbo.sp_creatediagram''),
			@id_renamediagram = object_id(N''dbo.sp_renamediagram''),
			@id_alterdiagram = object_id(N''dbo.sp_alterdiagram''), 
			@id_dropdiagram = object_id(N''dbo.sp_dropdiagram'')

		if @id_upgraddiagrams is not null
			select @InstalledObjects = @InstalledObjects + 1
		if @id_sysdiagrams is not null
			select @InstalledObjects = @InstalledObjects + 2
		if @id_helpdiagrams is not null
			select @InstalledObjects = @InstalledObjects + 4
		if @id_helpdiagramdefinition is not null
			select @InstalledObjects = @InstalledObjects + 8
		if @id_creatediagram is not null
			select @InstalledObjects = @InstalledObjects + 16
		if @id_renamediagram is not null
			select @InstalledObjects = @InstalledObjects + 32
		if @id_alterdiagram  is not null
			select @InstalledObjects = @InstalledObjects + 64
		if @id_dropdiagram is not null
			select @InstalledObjects = @InstalledObjects + 128
		
		return @InstalledObjects 
	END
	'


	GRANT EXECUTE ON dbo.fn_diagramobjects TO public
	DENY EXECUTE ON dbo.fn_diagramobjects TO guest
END

if IS_MEMBER('db_owner') = 1
BEGIN
	declare @val int
	select @val = 1
	if NOT EXISTS(	select major_id 
					from sys.extended_properties
					where major_id = object_id(N'dbo.sysdiagrams') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
	begin
		exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'TABLE', N'sysdiagrams', NULL, NULL
	end
	
	if NOT EXISTS(	select major_id 
					from sys.extended_properties
					where major_id = object_id(N'dbo.sp_upgraddiagrams') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
	begin
		exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_upgraddiagrams', NULL, NULL
	end
	
	if NOT EXISTS(	select major_id 
					from sys.extended_properties
					where major_id = object_id(N'dbo.sp_helpdiagrams') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
	begin
		exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_helpdiagrams', NULL, NULL
	end
	
	if NOT EXISTS(	select major_id 
					from sys.extended_properties
					where major_id = object_id(N'dbo.sp_helpdiagramdefinition') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
	begin
		exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_helpdiagramdefinition', NULL, NULL
	end
	
	if NOT EXISTS(	select major_id 
					from sys.extended_properties
					where major_id = object_id(N'dbo.sp_creatediagram') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
	begin
		exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_creatediagram', NULL, NULL
	end
	
	if NOT EXISTS(	select major_id 
					from sys.extended_properties
					where major_id = object_id(N'dbo.sp_renamediagram') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
	begin
		exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_renamediagram', NULL, NULL
	end
	
	if NOT EXISTS(	select major_id 
					from sys.extended_properties
					where major_id = object_id(N'dbo.sp_alterdiagram') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
	begin
		exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_alterdiagram', NULL, NULL
	end
	
	if NOT EXISTS(	select major_id 
					from sys.extended_properties
					where major_id = object_id(N'dbo.sp_dropdiagram') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
	begin
		exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_dropdiagram', NULL, NULL
	end
	
	if NOT EXISTS(	select major_id 
					from sys.extended_properties
					where major_id = object_id(N'dbo.fn_diagramobjects') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
	begin
		exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'FUNCTION', N'fn_diagramobjects', NULL, NULL
	end
END

/* Clean up */
/*
DROP FUNCTION dbo.fn_diagramobjects
DROP PROCEDURE dbo.sp_dropdiagram
DROP PROCEDURE dbo.sp_alterdiagram
DROP PROCEDURE dbo.sp_renamediagram
DROP PROCEDURE dbo.sp_creatediagram
DROP PROCEDURE dbo.sp_helpdiagramdefinition
DROP PROCEDURE dbo.sp_helpdiagrams
DROP TABLE dbo.sysdiagrams
DROP PROCEDURE dbo.sp_upgraddiagrams
*/

上述每个对象，最后都有下面两条授权语句。

GRANT EXECUTE ON dbo.sp_creatediagram TO public
DENY EXECUTE ON dbo.sp_creatediagram TO guest

由于RDS SQL Server 2008 R2的账号不具备grant权限，所以控制台创建的账号连接实例，添加这些对象的时候，执行grant会失败，提示没有权限。

 

    执行sp_creatediagram的定义，从抓取的trace看，执行到第一个授权时，事务就失败回滚了。

GRANT EXECUTE ON dbo.sp_creatediagram TO public

单独执行grant语句进行验证，确实是没有权限进行账号权限管理。

再次单独执行sp_creatediagram的定义语句，从trace看，没有事务的回滚记录。并且SQL窗口返回，命令执行成功（Command(s) completed successfully） 。应该是SQL Server内部trigger中，有相关错误处理机制，二次执行忽略了错误语句，跳过执行。

问题解决

拷贝文中对象定义脚本，将9个对象单独执行，每个均执行2遍即可成功。 对象创建完毕后，即可创建关系图。