阻止查询编辑器更改SQL Server Management Studio中的视图

When working with views in SQL Server Management Studio, I prefer to script the views into ALTER-statements, make my changes, format my code properly and then execute the statement. However, sometimes when I script a view that I have previously been working with, my formatting is lost and I see a whole lot of extended properties added to my view.

在SQL Server Management Studio中使用视图时,我更喜欢将视图编写为ALTER语句,进行更改,正确格式化代码然后执行语句。但是,有时当我编写一个我以前一直使用的视图时,我的格式会丢失,并且我看到我的视图中添加了大量扩展属性。

From this I gather, that somebody else has been using the SSMS Query Editor (designer) to edit the view, which is annoying since it completely breaks my formatting.

从此我得知,其他人一直在使用SSMS查询编辑器(设计师)来编辑视图,这很烦人,因为它完全破坏了我的格式。

Is there a way to block users from using the designer to alter views in SSMS? Ideally some database setting, but alternatively, some hack that would prevent the designer from opening the view.

有没有办法阻止用户使用设计器来改变SSMS中的视图?理想情况下,某些数据库设置,但另外,一些黑客会阻止设计者打开视图。

The pragmatic approach would simply be to talk to the users and make them stop using the designer, but I'm afraid old habits die hard, and I don't want to spend another minute reformatting code that I've already formatted many times before...

实用的方法只是与用户交谈,让他们停止使用设计师,但我担心*惯会变得很难,而且我不想再花一分钟重新格式化我已经多次格式化的代码...

3 个解决方案

#1

No there isn't a robust way of doing this.

没有一种强有力的方法可以做到这一点。

Adding constructs that the designer does not support but that do not change the semantics might be one possibility

添加设计者不支持但不改变语义的构造可能是一种可能性

WHERE (1 = (SELECT ROW_NUMBER() OVER (ORDER BY @@SPID)))

However it doesn't really work. On opening the view you see the message

但它并没有真正起作用。打开视图时,您会看到该消息

SQL text cannot be represented in the grid pane and diagram pane.

SQL文本无法在网格窗格和图表窗格中表示。

and these panes are blank but the SQL pane still appears and contains the reformatted SQL for editing. Also the above has the risk of changing the execution plan in a negative way too.

并且这些窗格是空白的,但SQL窗格仍然出现并包含重新格式化的SQL以进行编辑。此外,上述还存在以负面方式改变执行计划的风险。

Another approach might be to create a DDL trigger.

另一种方法可能是创建DDL触发器。

The default program_name strings I see for the designer vs a query window are "Microsoft SQL Server Management Studio" and "Microsoft SQL Server Management Studio - Query" so you could use.

我在设计器和查询窗口中看到的默认program_name字符串是“Microsoft SQL Server Management Studio”和“Microsoft SQL Server Management Studio - Query”,因此您可以使用。

CREATE TRIGGER NoAlterViewFromSSMS 
ON DATABASE 
FOR ALTER_VIEW
AS 
   IF APP_NAME() = 'Microsoft SQL Server Management Studio'
   BEGIN
       RAISERROR ('Please don''t use the designer to ALTER views',16, 1)
       ROLLBACK
   END
GO

But this will not fire until they attempt to save and your co workers might be highly annoyed. The AppName used by management studio is configurable anyway so this could also be circumvented.

但是,在他们试图挽救并且你的同事可能非常恼火之前,这不会开火。管理工作室使用的AppName无论如何都是可配置的,所以这也可以被规避。

The only other option I can think of would be to search the Connect site for bugs that prevent the designer from opening (I vaguely remember one with nested comment syntax) but even if you find one you are at risk that they will be fixed in a future service pack.

我能想到的唯一另一个选择是在Connect网站上搜索阻止设计者打开的错误(我依稀记得有一个嵌套注释语法),但即使你找到一个你有风险,他们将被修复未来的服务包。

#2

The application name of sql server when you use query for alter your view, is Microsoft SQL Server Management Studio - Query and application name of sql server when you use designer for alter your view is Microsoft SQL Server Management Studio.

当您使用查询来更改视图时,sql server的应用程序名称是Microsoft SQL Server Management Studio - 当您使用设计器来更改视图时,sql server的查询和应用程序名称是Microsoft SQL Server Management Studio。

You can crate DDL Trigger for ALTER_VIEW in order to check APP_Name() and limit your user to use query instead of designer.

您可以为ALTER_VIEW创建DDL触发器以检查APP_Name()并限制您的用户使用查询而不是设计器。

CREATE TRIGGER LimitUseDesignerForView ON DATABASE 
FOR ALTER_VIEW
AS 
   IF APP_NAME() = 'Microsoft SQL Server Management Studio'
   BEGIN
       RAISERROR ('Use query in order to alter your view',16, 1)
       ROLLBACK
   END
GO

You can also get list of sql server views that use designer by following query:

您还可以通过以下查询获取使用设计器的sql server视图列表:

SELECT DISTINCT OBJECT_NAME(ep.major_id) 
FROM sys.extended_properties ep
WHERE ep.name LIKE 'MS_DiagramPane%'

I suggest that you don't limit your user by APP_NAME() because user maybe use tools application in order to use view designer such as EMS and etc.

我建议您不要通过APP_NAME()限制您的用户,因为用户可能会使用工具应用程序来使用EMS等视图设计器。

You can limit your users by using limitation on DDL_EXTENDED_PROPERTY_EVENTS DDL trigger.

您可以通过使用DDL_EXTENDED_PROPERTY_EVENTS DDL触发器的限制来限制用户。

CREATE TRIGGER LimitUseDesignerForView ON DATABASE 
FOR DDL_EXTENDED_PROPERTY_EVENTS
AS 
    IF (EVENTDATA().value('(/EVENT_INSTANCE/Parameters/Param)[1]','nvarchar(max)') LIKE 'MS_DiagramPane%') BEGIN
        RAISERROR ('Use query in order to alter your view',16, 1)
        ROLLBACK 
    End
GO

#3

Perhaps you can revoke Alter Schema permission to the users, this should prevent them from running ALTER View statements.

也许您可以撤消对用户的Alter Schema权限,这可以防止它们运行ALTER View语句。

You could give ALTER SCHEMA permission to another user and allow yourself to Impersonate, then use EXECUTE AS to update your view:

您可以向其他用户授予ALTER SCHEMA权限并允许自己进行模拟,然后使用EXECUTE AS更新您的视图:

GRANT ALTER ON SCHEMA :: dbo TO user2;
REVOKE ALTER ON SCHEMA :: dbo TO user1;

GRANT IMPERSONATE ON USER:: user2 TO user1;

-- Alter view script
EXECUTE AS USER = 'user2';

ALTER VIEW ......

EXECUTE AS USER = 'user1';

#1